query that uses four linked tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query that uses four linked tables

Author

Topic

port43
Starting Member

19 Posts

Posted - 2011-11-03 : 23:13:56

Hello,
I am not a DBA but am trying to learn SQL as I go and all of my issues stem from a project that was designed years ago and was handed to me.
My question is on a query that needs to report after gathering information from four different tables.

[url]http://tonycoulter.com/images/projects.png[/url] is the table diagram

1) Projects contains only a single record for each project
2) Users contains only a single record for each user
3) Role contains only a single record for each RoleID but RoleOrder is an integer weight for each RoleID and can appear more than once
4) ProjectUsers can contain multiple records for each UserID depending on how many ProjectIDs they are involved with and how many RoleIDs they have in each project

I need to know all Projects that have less than three managers (Role.RoleOrder >= 70) with the output being:

ProjectID ProjectName ManagementCount FirstName LastName

Where ManagementCount is the count of ProjectUsers for an individual ProjectID with a Role.RoleOrder >= 70 and FirstName LastName is for the UserID that had the lowest Role.RoleOrder out of those returned.

Caveat, ManagementCount can be zero so FirstName LastName can be blank and that still needs to show in the output.

I cannot tell you how much I appreciate any help.

blessings,
Tony <><

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 01:57:28

[code]
SELECT p.ProjectID,p.ProjectName,p1.MgmntCnt,r.FirstName,r.Lastname
FROM Projects p
INNER JOIN (SELECT Project_ID,COUNT(CASE WHEN r.RoleOrder >= 70 THEN u.UserID ELSE NULL END) AS MgmntCnt,MIN(RoleOrder) AS MinRole
FROM ProjectUsers pu
JOIN User u
ON u.UserID = pu.UserID
JOIN Role r
ON r.RoleID = u.RoleID
GROUP BY Project_ID
HAVING COUNT(CASE WHEN r.RoleOrder >= 70 THEN u.UserID ELSE NULL END)< 3
)p1
ON p1.Project_ID = p.Project_ID
CROSS APPLY (SELECT TOP 1 FirstName, LastName
FROM User u
JOIN Role r
ON r.RoleID= u.RoleID
AND r.RoleOrder=p1.MinRole)r
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

port43
Starting Member

19 Posts

Posted - 2011-11-04 : 02:27:46

Wow, way beyond my skill level. Here is the output on execute (I had to change two table name 'User' to 'Users'):


/*------------------------
SELECT p.ProjectID,p.ProjectName,p1.MgmntCnt,r.FirstName,r.Lastname
FROM Projects p
INNER JOIN (SELECT Project_ID,COUNT(CASE WHEN r.RoleOrder >= 70 THEN u.UserID ELSE NULL END) AS MgmntCnt,MIN(RoleOrder) AS MinRole
           FROM ProjectUsers pu
            JOIN Users u
            ON u.UserID = pu.UserID
            JOIN Role r
            ON r.RoleID = u.RoleID
            GROUP BY Project_ID
            HAVING COUNT(CASE WHEN r.RoleOrder >= 70 THEN u.UserID ELSE NULL END)< 3
            )p1
           ON p1.Project_ID = p.Project_ID
CROSS APPLY (SELECT TOP 1 FirstName, LastName
             FROM Users u
             JOIN Role r
             ON r.RoleID= u.RoleID
             AND r.RoleOrder=p1.MinRole)r



------------------------*/
Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near 'APPLY'.
Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near 'r'.

blessings,
Tony <><

port43
Starting Member

19 Posts

Posted - 2011-11-04 : 02:32:05

I also had to remove '_' from 'Project_ID' in a few places and change 'u.RoleID' in the JOINs to 'pu.RoleID' since RoleID doesn't exist in the Users table.

Now the query looks like:


SELECT p.ProjectID,p.ProjectName,p1.MgmntCnt,r.FirstName,r.Lastname
FROM Projects p
INNER JOIN (SELECT ProjectID,COUNT(CASE WHEN r.RoleOrder >= 70 THEN u.UserID ELSE NULL END) AS MgmntCnt,MIN(RoleOrder) AS MinRole
           FROM ProjectUsers pu
            JOIN Users u
            ON u.UserID = pu.UserID
            JOIN Role r
            ON r.RoleID = pu.RoleID
            GROUP BY ProjectID
            HAVING COUNT(CASE WHEN r.RoleOrder >= 70 THEN u.UserID ELSE NULL END)< 3
            )p1
           ON p1.ProjectID = p.ProjectID
CROSS APPLY (SELECT TOP 1 FirstName, LastName
             FROM Users u
             JOIN Role r
             ON r.RoleID= pu.RoleID
             AND r.RoleOrder=p1.MinRole)r

and the error(s) are the same.

blessings,
Tony <><

port43
Starting Member

19 Posts

Posted - 2011-11-04 : 04:49:27

I have been monkying around with this all night and only one thing is for sure - I will sleep like a rock today since my brain has checked out.

The logic is making sense to me as I split out little pieces and check them individually but I just don't get the whole "incorrect syntax" thing.



/*------------------------
SELECT p.ProjectID,p.ProjectName,p1.MgmntCnt,r.FirstName,r.Lastname
FROM Projects p
INNER JOIN (SELECT ProjectID,COUNT(CASE WHEN r.RoleOrder >= 70 THEN u.UserID ELSE NULL END) AS MgmntCnt,MIN(RoleOrder) AS MinRole
           FROM ProjectUsers pu
            JOIN Users u
            ON u.UserID = pu.UserID
            JOIN Role r
            ON r.RoleID = pu.RoleID
            GROUP BY ProjectID
            HAVING COUNT(CASE WHEN r.RoleOrder >= 70 THEN u.UserID ELSE NULL END)< 3
            ) p1
           ON p1.ProjectID = p.ProjectID
CROSS APPLY (SELECT TOP 1 FirstName, LastName
             FROM Users u
             JOIN Role r
             ON r.RoleID = pu.RoleID
             AND r.RoleOrder = p1.MinRole) r



------------------------*/
Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near 'APPLY'.
Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near 'r'.

blessings,
Tony <><

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 05:29:19

Are you using SQL 2005 and above? APPLY works only from SQL 2005 onwards

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

port43
Starting Member

19 Posts

Posted - 2011-11-04 : 05:50:47

I assumed I was but I see now that the only thing that is 2005 is my MS SQL Server Management Studio - my actual database is MS SQL Server Enterprise Edition V8.00.818

crap.

blessings,
Tony <><
----------
"The problem with the internet is you never know if what you're reading is true or not. "
-- Abraham Lincoln

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 05:53:36

oh..then its 2000 and apply is not available
then modify like


SELECT p.ProjectID,p.ProjectName,p1.MgmntCnt,r.FirstName,r.Lastname
FROM Projects p
INNER JOIN (SELECT ProjectID,COUNT(CASE WHEN r.RoleOrder >= 70 THEN u.UserID ELSE NULL END) AS MgmntCnt,MIN(RoleOrder) AS MinRole
           FROM ProjectUsers pu
            JOIN Users u
            ON u.UserID = pu.UserID
            JOIN Role r
            ON r.RoleID = pu.RoleID
            GROUP BY ProjectID
            HAVING COUNT(CASE WHEN r.RoleOrder >= 70 THEN u.UserID ELSE NULL END)< 3
            ) p1
           ON p1.ProjectID = p.ProjectID
INNER JOIN(SELECT RoleOrder,FirstName, LastName
             FROM Users u
             JOIN Role r
             ON r.RoleID = pu.RoleID
             ) r
ON r.RoleOrder = p1.MinRole

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

port43
Starting Member

19 Posts

Posted - 2011-11-04 : 06:00:53

New error:


/*------------------------
SELECT p.ProjectID,p.ProjectName,p1.MgmntCnt,r.FirstName,r.Lastname
FROM Projects p
INNER JOIN (SELECT ProjectID,COUNT(CASE WHEN r.RoleOrder >= 70 THEN u.UserID ELSE NULL END) AS MgmntCnt,MIN(RoleOrder) AS MinRole
           FROM ProjectUsers pu
            JOIN Users u
            ON u.UserID = pu.UserID
            JOIN Role r
            ON r.RoleID = pu.RoleID
            GROUP BY ProjectID
            HAVING COUNT(CASE WHEN r.RoleOrder >= 70 THEN u.UserID ELSE NULL END)< 3
            ) p1
           ON p1.ProjectID = p.ProjectID
INNER JOIN(SELECT RoleOrder,FirstName, LastName
             FROM Users u
             JOIN Role r
             ON r.RoleID = pu.RoleID
             ) r
ON r.RoleOrder = p1.MinRole
------------------------*/

Msg 107, Level 16, State 2, Line 1
The column prefix 'pu' does not match with a table name or alias name used in the query.

but that doesn't seem to make sense since there's no 'pu' on line 1

blessings,
Tony <><
----------
"The problem with the internet is you never know if what you're reading is true or not."
-- Abraham Lincoln

port43
Starting Member

19 Posts

Posted - 2011-11-04 : 06:30:34

I've found that it's not a 'pu' in the first line per se but
it's the 'pu.RoleID' in the second INNER JOIN


SELECT p.ProjectID,p.ProjectName,p1.MgmntCnt,r.FirstName,r.Lastname
FROM Projects p
INNER JOIN (SELECT ProjectID,COUNT(CASE WHEN r.RoleOrder >= 70 THEN u.UserID ELSE NULL END) AS MgmntCnt,MIN(RoleOrder) AS MinRole
           FROM ProjectUsers pu
            JOIN Users u
            ON u.UserID = pu.UserID
            JOIN Role r
            ON r.RoleID = pu.RoleID
            GROUP BY ProjectID
            HAVING COUNT(CASE WHEN r.RoleOrder >= 70 THEN u.UserID ELSE NULL END)< 3
            ) p1
           ON p1.ProjectID = p.ProjectID
INNER JOIN(SELECT RoleOrder,FirstName, LastName
             FROM Users u
             JOIN Role r
             ON r.RoleID = pu.RoleID
             ) r
ON r.RoleOrder = p1.MinRole

I tried making it ProjectUsers.RoleID but it's the same type of error.

blessings,
Tony <><
----------
"The problem with the internet is you never know if what you're reading is true or not."
-- Abraham Lincoln

port43
Starting Member

19 Posts

Posted - 2011-11-04 : 06:41:17

I modified it to be


INNER JOIN(SELECT RoleOrder,FirstName, LastName
             FROM Users u, ProjectUsers pu
             JOIN Role r
             ON r.RoleID = pu.RoleID
             ) r

and now it executes but the output is one ProjectID, one ProjectName, and the correct MgmntCount for every person in the Users table followed by the same for the next ProjectID. Millions and millions of records returned. eek.

blessings,
Tony <><
----------
"The problem with the internet is you never know if what you're reading is true or not."
-- Abraham Lincoln

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 07:53:27

[code]
SELECT p.ProjectID,p.ProjectName,p1.MgmntCnt,r.FirstName,r.Lastname
FROM Projects p
INNER JOIN (SELECT ProjectID,COUNT(CASE WHEN r.RoleOrder >= 70 THEN u.UserID ELSE NULL END) AS MgmntCnt,MIN(RoleOrder) AS MinRole
FROM ProjectUsers pu
JOIN Users u
ON u.UserID = pu.UserID
JOIN Role r
ON r.RoleID = pu.RoleID
GROUP BY ProjectID
HAVING COUNT(CASE WHEN r.RoleOrder >= 70 THEN u.UserID ELSE NULL END)< 3
) p1
ON p1.ProjectID = p.ProjectID
INNER JOIN(SELECT ProjectID,RoleOrder,FirstName, LastName
FROM ProjectUsers pu
JOIN Users u
ON u.UserID = pu.UserID
JOIN Role r
ON r.RoleID = pu.RoleID
) r
ON r.RoleOrder = p1.MinRole
AND r.ProjectID = p.ProjectID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

port43
Starting Member

19 Posts

Posted - 2011-11-04 : 08:06:35

You, Sir, are my favorite person of the week! Heck, make it the month!

Thank you so much for your help - I learned quite a bit tonight. Is there any way I can repay you that doesn't involve cash?

blessings,
Tony <><
----------
"The problem with the internet is you never know if what you're reading is true or not."
-- Abraham Lincoln

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 08:09:29

quote:
Originally posted by port43

You, Sir, are my favorite person of the week! Heck, make it the month!

Thank you so much for your help - I learned quite a bit tonight. Is there any way I can repay you that doesn't involve cash?

blessings,
Tony <><
----------
"The problem with the internet is you never know if what you're reading is true or not."
-- Abraham Lincoln

Welcome

I'll ask if I need any favour

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources