LEFT JOIN question

Question

Post reply

LEFT JOIN question

Bob Loblaw

SSC Enthusiast

Points: 131
More actions
January 4, 2007 at 1:17 pm

#171729

Hi,
I'm trying to figure out some confusion regarding left joins.
I have table A, which I want to join to tables B and C.
SELECT COUNT(*) FROM A
and
SELECT COUNT(*) FROM A LEFT JOIN B ON A.ID = B.ID LEFT JOIN C ON A.ID = C.ID
return different results. How can I make the join return ONLY the number of rows in table A?
I want A to be the parent table and I want no duplication of rows, as I'm using the result of the join as a fact table for a cube, so when there are duplicated rows in table A the totals for A's columns are wrong.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 1

Try this

SELECT count(distinct a.ColumnA) FROM A LEFT JOIN B ON A.ID = B.ID LEFT JOIN C ON A.ID = C.ID

Change ColumnA to any column from table A.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 2

Your tables B or C have more that one row that matches a single row in A. For instance:

Table A
ID
-----
A 
B
C

Table B
PK_ID    ID
-------  -----
1        A
2        B
3        B
4        C

Just doing Count(*) is going to count all rows that match the given criteria. SELECT COUNT(*) FROM A LEFT JOIN B ON A.ID = B.ID in this example will return 4.

If it is as simple as you say, then just do the SELECT COUNT(*) FROM A; there is no need to outer join to B and C.

But I suspect your query is not that simple. You probably have some WHERE criteria on B and/or C, right? Perhaps something like this will work?

SELECT COUNT(*) FROM A WHERE A.ID IN
  (SELECT B.ID FROM B WHERE B.something = whatever or B.Something IS NULL)

Hope this helps

Mark

Bob Loblaw SSC Enthusiast Points: 131 More actions · Answer 3

I was only using the 'select count' as an illustration - the actual query is:

SELECT A.*, B.COL1, B.COL2, C.COL1, C.COL2

FROM

TABLE1 A JOIN TABLE2 B ON A.ID = B.ID

JOIN TABLE3 C ON A.ID = C.ID

This query returns too many records, as I only want ONE row for each single row in the 'parent' table.

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 4

Bob;

But the same condition applies. If their are 2 rows in B that match to 1 row in A, you will have 2 rows returned since there will different values in B values. Same with table C.

Mark

carie dobson SSC Enthusiast Points: 178 More actions · Answer 5

Agreed, Mark is correct. You are most likely seeing multiplicative results. To test, make sure you select the fields in your JOIN clauses, and try ordering by them as well. This will make it obvious.

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 6

obviously this applies equally to inner joins.

www.sql-library.com[/url]

Vladan SSC-Insane Points: 22072 More actions · Answer 7

If you want to return only one row for each row in table A, then you have to decide what should happen to the rest of data selected with the statement. There are several possibilities:

a) you know that by design the values of selected columns from joined tables (B.COL1, B.COL2, C.COL1, C.COL2) will always be the same for all rows with the same A.PK_col. Then you can use GROUP BY, and list all selected columns in it.

In case of different values you'll get several rows for one row in A.

b) you want some aggregate of the data in tables B and C. Then use GROUP BY only with columns of A, and an appropriate function with columns from tables B and C - SUM, AVG... or maybe MIN or MAX.

c) data in tables B and C are string data and you want to concatenate them for each row in table A and show them as a delimited list. Then you have to rewrite the query.

d) for each row in table A, you want to take into account only the newest (last added) row of those joined from tables B and C. Then you have to rewrite the query.

And there can be other requirements I didn't think about... As you see, lots of possibilities. Please let us know what result do you need, then we can start thinking about how to solve it.

PS. Please also post your DDL (CREATE TABLE statements) and some sample data (INSERT INTO..) in order to help us help you. Thanks.