Is the Right Join the WRONG Join?? 1

[ItHurtsWhenIThink]

SELECT
U.FName, U.LName, U.EmployeeID,U.UserID, Count(D.DrillTime)
FROM tr_Drills AS D INNER JOIN Tr_Individuals AS I ON D.DrillID = I.DrillID
RIGHT JOIN Users AS U ON I.MemberID = U.UserID
WHERE D.DrillTime>=3 AND D.DeptID=1
Group By U.LName, U.FName,U.EmployeeID,U.UserID
Order By U.LName

(tables listed at bottom of page)

This statement should get me all the users in the Users table.... I think (ouchhh)

I want to get every user listed and then include how many drills they attended where the drillTime is three hours or greater (not aggragate).

The result provides only those where there is a match in the Tr_Individuals table.

Any thoughts???

Tables:

Users
UserID (int)
DeptID (int)
LName
FName
EmployeeID (char)


tr_Drills
DrillID (int)
DeptID (int)
DrillDate (smalldate)
DrillTime (numeric)


tr_Individuals
CrewID
DrillID
MemberID
UnitID

 

[r937]

i hate RIGHT OUTER JOINs, always have, always will

my mind just naturally prefers to work from left to right

start with the users table, and use LEFT OUTER JOINs

SELECT U.FName
     , U.LName
     , U.EmployeeID
     , U.UserID
     , COUNT(D.DrillTime)
  FROM Users AS U
LEFT OUTER
  JOIN Tr_Individuals AS I
    ON I.MemberID = U.UserID
LEFT OUTER
  JOIN tr_Drills AS D 
    ON D.DrillID = I.DrillID  
   AND D.DrillTime >= 3 
   AND D.DeptID = 1
GROUP
    BY U.LName
     , U.FName
     , U.EmployeeID
     , U.UserID
ORDER 
    BY U.LName

notice the conditions on D are in the ON clause -- you had them in the WHERE clause which is why you weren't getting outer rows

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ItHurtsWhenIThink]

Awsome... yeah that left brain thing. LOL

It worked great. One last question for you, if you don't mind.

The DeptID filtering. I really need to filter the Users table, since the DB is used for many different agencies. So, I really need all users from just one agency. in this case it was where DeptID=1.

Mike...

 

[r937]

but if DeptID isn't in the users table, then you gots to use INNER joins, yes?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[NastiaMurzin]

select * from users, tr_drills, tr_individuals where users.deptid = tr_drills.deptid and tr_drills.drillid = tr_individuals.drillid and tr_drills.drilltime >= 3.00 order by tr_drills.drillid;

no?

 

[markros]

Please check this blog

http://beyondrelational.com/blogs/n...-t-bring-all-records-from-the-left-table.aspx

if you want to get all users, you need to use LEFT JOIN and move filtering conditions into JOIN clause as r937 indicated.

PluralSight Learning Library

 

[ItHurtsWhenIThink]

DeptID is in the Users table. I tried using the Right Join, but got horrible results.

 

[r937]

DeptID is in the Users table

i would like to change my query

SELECT U.FName
     , U.LName
     , U.EmployeeID
     , U.UserID
     , COUNT(D.DrillTime) AS how_many_drills
  FROM Users AS U
LEFT OUTER
  JOIN Tr_Individuals AS I 
    ON I.MemberID = U.UserID
LEFT OUTER
  JOIN tr_Drills AS D 
    ON D.DrillID = I.DrillID 
   AND D.DrillTime >= 3  
 [red]WHERE U.DeptID = 1[/red]
GROUP
    BY U.LName
     , U.FName
     , U.EmployeeID
     , U.UserID
ORDER 
    BY U.LName

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ItHurtsWhenIThink]

This is what I finally came up with.

Thank you all for your great input!


select u.lname,u.fname,u.employeeid,isnull(cnt,0) as drills
, shift,unitid
from users U
Left join
(
select count (*) as cnt, Memberid ,deptid
from tr_drills td , tr_individuals ti
where drilltime >= 3 and td.drillid = ti.drillid
AND drilldate between '3/1/2010' and '4/1/2010'
group by memberid,deptid
)
as Q1
on
u.deptid=Q1.deptid and u.userid = Q1.memberid
where U.DeptID=1 AND U.Enable='Yes'