Problems with an Outer join 1

[lsgtechuser]

I use various different outer joins frequently, but for some reason i can't figure this out.

select y.lname,y.fname, count(distinct phone), count(distinct familyid)
from TownTbl x full outer join
(select distinct lname, fname from nametbl) y on x.lname= y.lname and x.fname= y.fname
where income = 'R'
AND 99 = 'Y'
group by y.lname,y.fname order by y.lname,y.fname

there are 1600 distinct lname and fnames
in nametbl (i've changed the names of the fields for the post) which would mean I should get exactly 1600 records back (unless i'm missing something obvious), but i get 1595. when i run the statements separately i get 1600 from one and 1595 for the other, i'm trying to combine the two and get null values in the count columns for instances which the where clause would exclude

I can't use a count(case ... statement here b/c i need to do count(distinct). any help would be appreciated, thanks in advance

 

[gmmastros]

Try moving the WHERE conditions to the ON clause. Like this...

select y.lname,y.fname, count(distinct phone), count(distinct familyid) 
from   TownTbl x 
       full outer join 
           (select distinct lname, 
                   fname 
           from nametbl) y 
           on  x.lname= y.lname 
           and x.fname= y.fname
           [!]And income = 'R' 
           AND 99 = 'Y' [/!]
group by y.lname,y.fname 
order by y.lname,y.fname

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

Man George is on it!!! I hope you don't mind me calling you George

<.

 

[gmmastros]

That's my name. Feel free to call me George. Just don't call me late for dinner!

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[lsgtechuser]

Thanks George, that worked Great! I've run plenty of queries in the past with the added criteria in the where clause after the join and they've worked fine, any idea on why it had to be done this way? Also... is keeping everything in the on clause "proper"/standard?
Just trying to get better.

 

[gmmastros]

It really depends on what you are trying to do. Sometimes the where clause is the appropriate place and some times it's not.

Let me try to explain this with an example. In the code I present here, everything is using a temp table, so you should be able to copy/paste to query analyzer to see what the results are. This will not affect any database.

[green]-- Set up 2 tables to test with[/green]
Create Table #T1 (Id Int, Data VarChar(20))
Insert Into #T1 Values(1, 'Blue')
Insert Into #T1 Values(2, 'Green')

Create Table #T2 (Id int, Data VarChar(20))
Insert Into #T2 Values(1, 'Hamburger')
Insert Into #T2 Values(3, 'Pizza')

[green]-- If you outer join the 2 tables 
-- based on ID, then you would expect 
-- to get 3 records (and you do).[/green]
Select *
From   #T1
       Full Outer Join #T2
         On #T1.Id = #T2.Id

[tt][blue]
Id    Data      Id     Data
---   --------- ------ --------------------
1     Blue      1      Hamburger
2     Green     NULL   NULL
NULL  NULL      3      Pizza
[/blue][/tt]

[green]-- Now, add a where clause, and the records
-- are filtered such that the NULL
-- records are NOT returned[/green]

Select *
From   #T1
       Full Outer Join #T2
         On #T1.Id = #T2.Id
Where  #T1.Data = 'Blue'

[tt][blue]
Id    Data    Id    Data
----- ------- ----- ----------
1     Blue    1     Hamburger
[/blue][/tt]

[green]--Now, put the condition in the ON clause
-- and see that there are actually 3 records 
-- returned[/green]
Select *
From   #T1
       Full Outer Join #T2
         On  #T1.Id = #T2.Id
         And #T1.Data = 'Blue'

[tt][blue]
Id    Data    Id      Data
----- ------- ------- --------------------
1     Blue    1       Hamburger
2     Green   NULL    NULL
NULL  NULL    3       Pizza
[/blue][/tt]

Does this make sense now?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom