Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Need help with outer joins Expand / Collapse
Author
Message
Posted Thursday, March 13, 2008 12:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 07, 2012 2:06 PM
Points: 14, Visits: 15
I have 3 tables - User (has CityId, GroupId), City, Group. I am trying to run a query that would return me a count of users for all cities and all groups.
City Group TotalUsers
Miami Marketing 50
Miami Sales 0
Miami Accounting 5
NY Marketing 0
NY Sales 25
NY Accounting 0
PHL Marketing 12
PHL Sales 4
PHL Accounting 75

I tried this:

Select c.Name, g.Name, Count(u.CityId)
From Group g Left outer join
(Select GroupID, CityID from User
where Joindate >= @startdate And JoinDate <= @enddate) u
right outer join City c on c.ID = u.CityID
on g.ID = u.GroupID
Group by c.Name, g.Name
ORDER BY c.Name, g.Name

but I don't get all the rows I need.

Can someone please help?

Thanks.

- SG
Post #468954
Posted Thursday, March 13, 2008 12:57 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Try this:
Select c.Name, g.Name, Count(u.CityId)
From User u
Right outer Join Group g ON (g.ID = U.GroupID
And u.Joindate Between @startdate and @enddate)
Right outer Join City c ON (c.ID = u.CityID)
Group by c.Name, g.Name
ORDER BY c.Name, g.Name



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #468962
Posted Thursday, March 13, 2008 1:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 07, 2012 2:06 PM
Points: 14, Visits: 15
RBarryYoung,

Thanks for the prompt reply. But that doesn't work. It brings back users only for cities and groups that match the date criteria. If there are no users in a specific city or group those rows are not returned.

- sg
Post #468967
Posted Thursday, March 13, 2008 1:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 7,081, Visits: 14,672
Are you looking to start from a list of all groups matched up against all cities? If not - where's the list of all groups by city?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #468973
Posted Thursday, March 13, 2008 1:10 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:34 PM
Points: 15,442, Visits: 9,588
select city.name as City, group.name as [Group], 
isnull(subqty.qty, 0) as Qty
from dbo.City
cross join dbo.Group
left outer join
(select cityid, groupid, count(*) as Qty
from dbo.User
where joindate >= @startdate
and joindate < @enddate
group by cityid, groupid) SubQty
on city.cityid = subqty.cityid
and group.groupid = subqty.groupid
order by city, group

Try that. (You'll need to format it to fit your needs, of course.)


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #468975
Posted Thursday, March 13, 2008 1:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 07, 2012 2:06 PM
Points: 14, Visits: 15
That's right.... I am trying to get a count of users for each of the groups in each of the cities. So if there are no users in Marketing group in Miami, that row would still up with a count of 0.
So in this example, since I have 3 cities and 3 groups, there will be 9 rows returned irrespective of how many rows exist in the user table.
Post #468976
Posted Thursday, March 13, 2008 1:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 7,081, Visits: 14,672
About the same as Gsquared - either should work, I think

select cityname as City, groupname as [Groupname], 
isnull(subqty.qty, 0) as Qty
from (select groupid, group.name groupname, city.name Cityname, cityid
from dbo.Group CROSS JOIN city) cartesianX
left outer join
(select cityid, groupid, count(*) as Qty
from dbo.User
where joindate >= @startdate
and joindate < @enddate
group by cityid, groupid) SubQty
on cartesianX.cityid = subqty.cityid
and cartesianX.groupid = subqty.groupid
order by cityname, groupname



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #468979
Posted Thursday, March 13, 2008 1:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 07, 2012 2:06 PM
Points: 14, Visits: 15
Thanks for the reply gsquared.

The number of rows returned are correct... but somehow the count is messed up. I am trying to figure out why. The total number of users matching the date range is not the same as the total of all users returned by city/group.


Let me first try to understand what does cross join do?
Post #468982
Posted Thursday, March 13, 2008 1:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:33 AM
Points: 7,081, Visits: 14,672
Cross JOIN is also what is called a "cartesian product". Meaning - it essentially matches up every row of table X with ever row of table Y.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #468987
Posted Thursday, March 13, 2008 1:35 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:34 PM
Points: 15,442, Visits: 9,588
s gupta (3/13/2008)
Thanks for the reply gsquared.

The number of rows returned are correct... but somehow the count is messed up. I am trying to figure out why. The total number of users matching the date range is not the same as the total of all users returned by city/group.


Let me first try to understand what does cross join do?


Try running the sub-query (the derived table) without the outer query, and see if the data from it makes sense.

I also realized that, in my query, I used "<" where you had "<=". That might make a difference.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #468991
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse