Need help with outer joins

  • 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

  • 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

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • 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

  • 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?

  • 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

  • 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.

  • 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?

  • 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?

  • 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?

  • 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

  • Thanks for the reply "Hall of Fame".

    This one doesn't seem to work as well (the same as gsquared's). The number of rows returned are fine but totals don't match up.

  • So that's the same as "Full join" right?

  • (As a side note, "Hall of Fame" and other such titles are ranks on this page, the name of the person writing the post is above that, in the pale blue part.)

    - 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

  • The sub-query returns the right number of rows. I had fixed the where clause when I tried your suggestion to use >= & <=.

  • Name's Matt by the way.

    What are you trying to match this up to?

    The statement earlier (The total number of users matching the date range is not the same as the total of all users returned by city/group.) would sound like an "of course they wouldn't" to me. After all - if the date filter didn't remove some users from the count, then why would you use it?

    ----------------------------------------------------------------------------------
    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?

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply