Need help with outer joins

Question

Post reply

Need help with outer joins

Viewing 11 posts - 16 through 25 (of 25 total)

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

s gupta SSC Enthusiast Points: 193 More actions · Answer 1

Thanks for pointing that out.. I feel so stupid not to notice that.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

s gupta (3/13/2008)
So that's the same as "Full join" right?

No. not at all. In a cross join - there's no join criteria whatsoever.

In a FULL join, there are join criteria, and the rows that match show up with values on both sides. The mismatches on either side are included, but the other table's columns are NULL.

don't sweat the name thing - happens a lot!

----------------------------------------------------------------------------------
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 SSC Guru Points: 260824 More actions · Answer 3

Full join means include all rows from both tables. Cross join means include all combinations of rows from both tables.

A full join has a key relationship between the tables. For example:

select *

from dbo.Customers

full outer join dbo.Orders

on Customers.CustomerID = Orders.CustomerID

This will return all customers, regardless of whether they have any records in the Orders table, and all orders, regardless of whether they have a customer ID in them. There is a relationship between the tables based on the customer ID.

A cross join, on the other hand, gives you all possible combinations of the rows, without regard to a relationship between the tables.

Thus, a cross join between cities and groups give you all cities and all groups in all possible combinations.

- 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 SSC Enthusiast Points: 193 More actions · Answer 4

Thanks Matt & Gsquared for the explanations. Learnt something new... can't believe I've been doing Sql for so long without knowing this.

s gupta SSC Enthusiast Points: 193 More actions · Answer 5

Matt Miller (3/13/2008)
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?

I am trying match the total of the count column to the total number of user rows matching the date range. So in my example, the Users table should have 171 rows falling in the date range (50+5+25+12+4+75)

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

Does that make sense?

Thanks.

- SG

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Yup - got it. Sounds like you already found the discrepancy, though - are you all set?

----------------------------------------------------------------------------------
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 SSC Enthusiast Points: 193 More actions · Answer 7

Matt Miller (3/13/2008)
Yup - got it. Sounds like you already found the discrepancy, though - are you all set?

Matt,

Unfortunately not.

Now that I understand what Cross join is supposed to do, I can make sense out the suggested query - both yours and GSquared's. It seems like it should work... but as I mentioned the totals are still off. The total of all the users in all cities/groups are not the same as the user rows returned for the date range.

I am checking to see if I made some silly mistake on my side in copying the query or if there is some data that is causing this to happen. Doesn't look like so far.

Are you guys sure this should work? Thanks for all the help.

- sg

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Well - I'm pretty darn sure that we're not "messing" with the numbers, since the sub-query is doing the tallying.

Have you checked if anyone has null values (or values not in the matching table) in the user row. User rows where groupID is null or CityID is null would NOT be getting included here. Same if an invalid cityID or group ID was in the user table.....

Also - I've heard of bad statistics or a screwed up index throwing off grouped counts. Perhaps rebuild your indexes?

----------------------------------------------------------------------------------
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 SSC Enthusiast Points: 193 More actions · Answer 9

Matt,

It was a silly mistake on my part. Your (and GSquared's) query works just fine and the numbers match up.

Thanks a lot for your help.

- sg

GSquared SSC Guru Points: 260824 More actions · Answer 10

Glad to hear it worked out.

- 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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

Ditto - thanks for the feedback!

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