SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help with outer joins


Need help with outer joins

Author
Message
s gupta
s gupta
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 19
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
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34866 Visits: 9518
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."
s gupta
s gupta
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 19
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
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29381 Visits: 19002
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?
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57519 Visits: 9730

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
s gupta
s gupta
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 19
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.
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29381 Visits: 19002
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?
s gupta
s gupta
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 19
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?
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29381 Visits: 19002
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?
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57519 Visits: 9730
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search