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 1:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 7, 2012 2:06 PM
Points: 14, Visits: 15
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.
Post #468993
Posted Thursday, March 13, 2008 1:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 7, 2012 2:06 PM
Points: 14, Visits: 15
So that's the same as "Full join" right?
Post #468999
Posted Thursday, March 13, 2008 1:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
(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
Post #469000
Posted Thursday, March 13, 2008 1:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 7, 2012 2:06 PM
Points: 14, Visits: 15
The sub-query returns the right number of rows. I had fixed the where clause when I tried your suggestion to use >= & <=.
Post #469001
Posted Thursday, March 13, 2008 1:42 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:56 PM
Points: 7,179, Visits: 15,774
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?
Post #469002
Posted Thursday, March 13, 2008 1:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 7, 2012 2:06 PM
Points: 14, Visits: 15
Thanks for pointing that out.. I feel so stupid not to notice that.
Post #469005
Posted Thursday, March 13, 2008 1:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:56 PM
Points: 7,179, Visits: 15,774
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?
Post #469006
Posted Thursday, March 13, 2008 1:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #469007
Posted Thursday, March 13, 2008 1:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 7, 2012 2:06 PM
Points: 14, Visits: 15
Thanks Matt & Gsquared for the explanations. Learnt something new... can't believe I've been doing Sql for so long without knowing this.
Post #469010
Posted Thursday, March 13, 2008 1:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 7, 2012 2:06 PM
Points: 14, Visits: 15
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
Post #469014
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse