SQL join on multiple table, statement confusion

Question

I have a query that join 4 tables like this :

SELECT A.NO_COMM,
       B.CODE_ART,
       C.NO_PROJ,
       D.RSN_SOC

FROM ACH_COMM           AS A LEFT OUTER JOIN
     ACH_COMM_ITEM      AS B ON B.NO_COMM = A.NO_COMM  JOIN
     FIN_FOUR           AS C ON C.NO_FOUR = A.NO_FOUR  JOIN
     ACH_COMM_ITEM_CMPT AS D ON D.NO_ITEM_COMM = B.NO_ITEM_COMM

WHERE        ...Whatever
GROUP BY     ...Everything

What i wanted to happens here is the following :

All the rows of table A are returned only once with the information of the other tables if those exist. So the number of rows returned should be equal to the amount of record in A. The NO_COMM witch is the primary key of table A shouldn't be displayed twice with the same number.

But i have the same NO_COMM displayed 167 times... a left join should be returning everything from the left table only once ? No ?

The real problem was the fact that an outer left join do not nessecarly end up returning a single row for each of the left table record.

no. you're grouping by "everything", so you'll get as many rows as the grouping allows. e.g. if you have (no_comm, code_art) as (1,2) and (1,3), that's two different groupings, even though the `1` is "repeated". — Marc B, Dec 07 '15 at 18:49
Possible duplicate of [Left join and Left outer join in SQL Server](http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server) — Tab Alleman, Dec 07 '15 at 20:22

score 2 · Accepted Answer · answered Dec 07 '15 at 18:50

2

a left join should be returning everything from the left table only once

No - a LEFT JOIN will return a record for each matching record in the right-hand table, or a single record if there are no matches on the right-hand side. Also, since you're using inner joins after the left join you will get even more records if there are multiple matches in C and D.

If you want a single record from the right-hand side you probably need a subquery to specify the "one" record you want (max based on some criteria?). In SQL Server you can use partitioning to specify a row number within each "group" based on some criteria, bur with multiple joined tables it can get quite complex.

answered Dec 07 '15 at 18:50

D Stanley

149,601
11
178
240

OK..... that could not have been so simple, and there is no such functions like "just pick the first you find, I don't care" so i will have to add a subquery for every var ? – Antoine Pelletier Dec 07 '15 at 19:14
Unfortunately not - if you want a single record from each group you need to specify how you are defining "first". If the choice is arbitrary you can use grouping functions like Max or Min but there's no "random" picker. – D Stanley Dec 07 '15 at 19:18

score 0 · Answer 2 · answered Dec 07 '15 at 18:49

0

If table ACH_COMM_ITEM (B) contains duplicate values for NO_COMM, this will duplicate those lines from ACH_COMM (A) too.

answered Dec 07 '15 at 18:49

Tammert

196
6

SQL join on multiple table, statement confusion

2 Answers2