January 4, 2007 at 1:17 pm
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.
January 4, 2007 at 1:35 pm
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.
January 4, 2007 at 1:39 pm
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
January 4, 2007 at 2:12 pm
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.
January 4, 2007 at 3:20 pm
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
January 8, 2007 at 9:20 am
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.
January 8, 2007 at 9:45 am
January 9, 2007 at 3:02 am
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply