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 (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 19
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.
s gupta
s gupta
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 19
So that's the same as "Full join" right?
GSquared
GSquared
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55329 Visits: 9730
(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
s gupta
s gupta
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 19
The sub-query returns the right number of rows. I had fixed the where clause when I tried your suggestion to use >= & <=.
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28235 Visits: 18999
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?
s gupta
s gupta
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 19
Thanks for pointing that out.. I feel so stupid not to notice that.
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28235 Visits: 18999
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
GSquared
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55329 Visits: 9730
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
s gupta
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 19
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
s gupta
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 19
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
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