• wolfkillj (12/27/2013)


    happy55 (12/27/2013)


    thanks for the reply.I am doing the same thing but still i am not geting all rows present in Table a .

    I am geting only those for which the count exists.

    Any other suggestions

    Well, at the risk of sounding rude, it's simply not possible to get fewer than all rows from Table A in the query you posted (and I re-posted, with the insignificant modification of leaving out a redundant ISNULL()). By definition, SELECT . . . FROM TableA LEFT OUTER JOIN TableB will return all rows from TableA and join to them the rows from TableB that satisfy the join condition(s). Some rows from TableA may be joined to one or many rows from TableB and some may be joined to zero rows from TableB, but ALL rows from TableA will appear in the result. There MUST be some other code at work here.

    A caveat, though - the *number* of rows in the result of the query you posted may be fewer than the *total* number of rows in Table A because of the aggregration. If there are multiple rows in Table A with id = 1, there will only be one row in the result with id = 1 because you are taking a COUNT() with GROUP BY a.id.

    If this is happening, your results may not be what you expect because every row in Table A will be joined to every row in Table B that satisfies the join condition. Look at this example, which differs from the original because there are two rows in Table A where id = 1:

    create table #a (id int)

    create table #b (id int, uniqueid int)

    insert into #a values (1), (2), (3), (4), (5), (1)

    insert into #b values (1, 2), (1, 3), (2, 4), (2, 5), (2, 6), (4, 1)

    select * from #a

    select a.id

    ,count(b.uniqueid) as n

    from #a a

    left outer join #b b on a.id = b.id

    group by a.id

    Result:

    idn

    14

    23

    30

    41

    50

    As you can see, there are six rows in Table A but only five rows in the output, and although there are only two rows in Table B where id = 1, the COUNT() function gives a result of 4. That's because the two rows in Table A where id = 1 were each joined with the two rows in Table B where id = 1 (two times two is four). If this is happening to you, you are probably deeper in the weeds than you seem to appreciate, and you'd probably better post your *actual* code, some sample data, and the expected result of your query so we can help you out.

    Jason Wolfkill