December 19, 2014 at 4:50 pm
I have two rather simple tables. One is a projects listing (directories) called Projects which has the following columns:
[ProjectID]
,[Name]
,[StatusID]
,[StartTime]
,[Deadline]
,[TimeEstimate]
,[Busy]
,[MoveCount]
,[Path]
,[Deleted]
,[FolderType]
The other table is called ProjectsTree and defines the parent child relationship of the directories and has just two columns as follows:
[ChildProject]
,[ProjectID]
I have a query that is very close to what I want, (it actually returns the correct data) but I would to write a query where I can use the name of the parent directory vs. its ProjectID.
SELECT Distinct [Name] FROM [EPDM].[dbo].[Projects]
Join [EPDM].[dbo].ProjectTree
ON ProjectTree.ChildProject=Projects.ProjectID where [EPDM].[dbo].[ProjectTree].[ProjectID] = 4 and [Deleted] = 0
This returns the directories that report to the directory with an ID of 4 and have not been deleted from the database (Deleted = 0). What I want is to replace ProjectID = 4 to a literal such as 'Projects'.
Any help would be appreciated. I am sure its pretty easy for many out there.
December 20, 2014 at 8:15 am
Hi
Well If i understand you correctly.
You want to filter (where clause) by Project.name.
Just looking at you table i think firstly your joins are incorrect, just by looking at your table column names.
I think what you are looking for is something like below
SELECT Distinct [Name] FROM [EPDM].[dbo].[Projects]
Join [EPDM].[dbo].ProjectTree
ON ProjectTree.ProjectID=Projects.ProjectID
where [EPDM].[dbo].[ProjectTree].[Name] = "Projects"
and [Deleted] = 0
Kind Regards
--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
December 20, 2014 at 8:15 am
If the above is not what you are looking for please provide some sample data.
--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
December 20, 2014 at 3:04 pm
Query did not work. ProjectTree does not have a column called Name
See attached images for tables and columns.
Also see query I am currently using and result.
Thanks for the assist.
December 20, 2014 at 4:54 pm
Thanks for posting the data, but if you could post it in consumable format, then people could help you a lot easier - and you would get tested solutions. This article will show you how to post in a way to get the best help:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you post CREATE TABLE and some INSERTS (enough for people to see the problem)... if you have confidential data in your table(s) feel free to obscure it (or if it's not necessary, leave it out).
December 21, 2014 at 9:30 am
Hopefully I have this correct per request.
USE [Test_DB]
GO
/****** Object: Table [dbo].[ProjectTree] Script Date: 12/21/2014 11:02:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProjectTree](
[ChildProject] [int] NOT NULL,
[ProjectID] [int] NOT NULL,
CONSTRAINT [aaaaaProjectTree1_PK] PRIMARY KEY CLUSTERED
(
[ChildProject] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO ProjectTree (ChildProject, ProjectID)
VALUES ('0','1');
INSERT INTO ProjectTree (ChildProject, ProjectID)
VALUES ('2','1');
INSERT INTO ProjectTree (ChildProject, ProjectID)
VALUES ('3','1');
USE [Test_DB]
GO
/****** Object: Table [dbo].[Projects] Script Date: 12/21/2014 11:05:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Projects](
[ProjectID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](255) NULL,
[Deleted] [bit] NOT NULL,
CONSTRAINT [aaaaaProjects1_PK] PRIMARY KEY NONCLUSTERED
(
[ProjectID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO Projects (Name, Deleted)
VALUES ('Projects','0');
INSERT INTO Projects (Name, Deleted)
VALUES ('Prometheous','0');
INSERT INTO Projects (Name, Deleted)
VALUES ('Genesis','0');
December 21, 2014 at 3:43 pm
So trying to post this from my phone
SELECT Distinct c.[Name]
FROM [EPDM].[dbo].[Projects] a
Join [EPDM].[dbo].[ProjectTree] b
ON B.ProjectID=A.ProjectID
join [EPDM].[dbo].[Projects] c
On b.childprojectId=c.projectid
Where a.name ='projects'
and a.[Deleted] = 0
Hope this is what you want
--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
December 21, 2014 at 4:45 pm
That did it! Thanks for the patience and assist. I really appreciate it.
December 22, 2014 at 12:14 am
No Problem at all
--------------------------------------------------------------------------------------------------------------------------------------------------------
To some it may look best but to others it might be the worst, but then again to some it might be the worst but to others the best.
http://www.sql-sa.co.za
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply