Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Posts
    9

    Unanswered: Left outer join sql

    Hi everybody,

    I have problem with understanding SQL written below.

    Firstly I create two table named as A and B.

    Code:
    create table #A (id_a varchar(10))
    insert into #A values('1')
    insert into #A values('2')
    insert into #A values('3')
    insert into #A values('4')
    insert into #A values('5')
    insert into #A values('6')
    
    create table #B (id_b varchar(10),fk_id_a varchar(10),amount varchar(10))
    insert into #B values('1','1','10')
    insert into #B values('2','1','10')
    insert into #B values('3','2','10')
    insert into #B values('4','3','10')
    insert into #B values('5','4','10')
    insert into #B values('6','5','10')
    insert into #B values('7','6','10')
    Now I want to select record count for id_a(in table #A) from table #B.
    I write SQL as;
    Code:
    select a.id_a,count(b.amount)  as cnt from #A a
    left outer join #B b  on b.fk_id_a = a.id_a
    group by a.id_a
    and get result as

    Code:
    id_a  cnt
    1	2
    2	1
    3	1
    4	1
    5	1
    6	1
    Now I want to get another result. Only difference is that, I want to ignore one row from table #B.
    Code:
    select a.id_a,count(b.amount) as cnt from #A a
    left outer join #B b  on b.fk_id_a = a.id_a
    where b.id_b <> 7
    group by a.id_a
    and get result as
    Code:
    id_a  cnt
    1	2
    2	1
    3	1
    4	1
    5	1
    but I can't see 6 in result set. I wrote Left outer Join to select all data from table #A. But it does not return data which I want.
    To get result which I want I tried another SQL as below;

    Code:
    select a.id_a,count(b.amount) as cnt from #A a
    left outer join #B b  on b.fk_id_a = a.id_a and b.id_b <> 7
    group by a.id_a
    and get result what I want.
    Code:
    id_a   cnt
    1	2
    2	1
    3	1
    4	1
    5	1
    6	0
    But I want to know why the first SQL
    Code:
    select a.id_a,count(b.amount) as cnt from #A a
    left outer join #B b  on b.fk_id_a = a.id_a
    where b.id_b <> 7
    group by a.id_a
    does not give the result what I want.

    Thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That SQL won't produce what you say it does. Do you mean ".... <> 6"?

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    because you put the condition in the WHERE clause, not in the ON clause. Terry Purcell wrote about outer join constructs years ago and I still refer folks to it all the time. Meet the experts: Terry Purcell on coding predicates in outer joins: A comparison of simple outer join constructs. Also, you can look at Robert Catterall's blog for some easy to understand explanations, Catterall Consulting.
    Dave

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm pretty certain I am right and you typoed.

    It is because the first SQL omits the row after the join had been performed so the row where ID = 6 is removed. The second SQL omits the row before the join has been performed so there is no row from b to join to but there is a row from a to display.

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Also, by moving your codition to the ON clause, you will not get exactly what you are looking for. The output would actually be 6,null. You'll have to use the coalesce function to return a 0 in case of null value.
    Dave

  6. #6
    Join Date
    Mar 2010
    Posts
    9
    Thank you for yours answers.

    I appreciate it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •