Left Outer JOIN with no multiple records of left table

Question

A[id, name]

id	name
1001	name1
1002	name2

B[id, productId, serviceId].

id	productId	serviceId
1001	p1	s1
1001	p1	s2
1001	p2	s2
1004	p1	s2

One id in B can have many products & many services. (A product can have many services and a service can have many products.)

My goal is get every id from table A with its associated products and services. But I should not get multiple id rows.

Left Outer join is giving me multiple rows of same ids.

SELECT A.ID, A.NAME, B.PRODUCTID, B.SERVICEID
FROM TABLE_A A
LEFT JOIN TABLE_B B
ON A.ID = B.ID 
WHERE some conditions ;

Aggregate them. Append `GROUP BY a.id` and use `GROUP_CONCAT()` to combine all products and services together. — The Impaler, Feb 09 '23 at 18:27
Does this answer your question? [What is the difference between "INNER JOIN" and "OUTER JOIN"?](https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join) — philipxy, Feb 10 '23 at 06:54
Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Feb 10 '23 at 06:57
Issues here are faqs. Please before considering posting: Pin down code issues via [mre]. Read manuals/references & google error messages & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] Reflect research in posts. — philipxy, Feb 10 '23 at 06:59
above question didn't exactly answer my question because I couldn't aggregate products and services properly to get unique ids after join too. — Adarsh Gupta, Feb 10 '23 at 11:47
Please clarify via edits, not comments. But that comment is not clear--why "because". Any again: when you pin down each place you get what you don't expect it will be a faq. The given link explains how joins work, and your post doesn't ask a clear question about join. Please act on the comments. See [How do comment replies work?](https://meta.stackexchange.com/q/43019/266284) to learn to use `@x` to notify 1 non-sole non-poster commenter `x` per comment about that comment. Posters, sole commenters & followers of posts always get notified. Without `@` other commenters get no notification. — philipxy, Feb 10 '23 at 15:43
[SQL Query to concatenate column values from multiple rows in Oracle](https://stackoverflow.com/q/4686543/3404097) "google error messages & many clear, concise & precise phrasings of your question/problem/goal" etc. There isn't even a question in your post. — philipxy, Feb 10 '23 at 15:53

score 0 · Answer 1 · answered Feb 10 '23 at 07:06

Try it like this:

SELECT ID, A_NAME, MAX(LIST_OF_PRODUCTS) "LIST_OF_PRODUCTS", MAX(LIST_OF_SERVICES) "LIST_OF_SERVICES"
FROM    
    (
        SELECT DISTINCT
                ID, A_NAME, 
                LISTAGG(CASE WHEN P_ID_RN = 1 THEN PRODUCT_ID END, ', ') WITHIN GROUP (Order By PRODUCT_ID) OVER(Partition By ID) "LIST_OF_PRODUCTS",
                LISTAGG(CASE WHEN S_ID_RN = 1 THEN SERVICE_ID END, ', ') WITHIN GROUP (Order By SERVICE_ID) OVER(Partition By ID) "LIST_OF_SERVICES"
        FROM
            (  SELECT  
                          a.ID, a.A_NAME,
                          b.PRODUCT_ID,
                          ROW_NUMBER() OVER(Partition By a.ID, b.PRODUCT_ID Order By PRODUCT_ID) "P_ID_RN",
                          b.SERVICE_ID,
                          ROW_NUMBER() OVER(Partition By a.ID, b.PRODUCT_ID, b.SERVICE_ID Order By b.PRODUCT_ID) "S_ID_RN"
                FROM      tbl_a a
                LEFT JOIN tbl_b b ON (a.ID = b.ID) 
            )
    )
WHERE     LIST_OF_PRODUCTS Is Not Null OR LIST_OF_SERVICES Is Not Null
GROUP BY  ID, A_NAME
ORDER BY  ID

... which with sample data as below ...

WITH
    tbl_a AS
        (
            Select 1001 "ID", 'Name 1' "A_NAME" From Dual Union All
            Select 1002 "ID", 'Name 2' "A_NAME" From Dual Union All
            Select 1003 "ID", 'Name 3' "A_NAME" From Dual Union All
            Select 1004 "ID", 'Name 4' "A_NAME" From Dual 
        ),
    tbl_b AS
        (
            Select 1001 "ID", 'P_1' "PRODUCT_ID", 'S_1' "SERVICE_ID" From Dual Union All
            Select 1001 "ID", 'P_1' "PRODUCT_ID", 'S_2' "SERVICE_ID" From Dual Union All
            Select 1001 "ID", 'P_1' "PRODUCT_ID", 'S_5' "SERVICE_ID" From Dual Union All
            Select 1002 "ID", 'P_4' "PRODUCT_ID", 'S_1' "SERVICE_ID" From Dual Union All
            Select 1002 "ID", 'P_4' "PRODUCT_ID", 'S_3' "SERVICE_ID" From Dual Union All
            Select 1003 "ID", 'P_2' "PRODUCT_ID", 'S_1' "SERVICE_ID" From Dual Union All
            Select 1003 "ID", 'P_3' "PRODUCT_ID", 'S_2' "SERVICE_ID" From Dual Union All
            Select 1004 "ID", 'P_1' "PRODUCT_ID", 'S_2' "SERVICE_ID" From Dual 
        )

... results:

ID	A_NAME	LIST_OF_PRODUCTS	LIST_OF_SERVICES
1001	Name 1	P_1	S_1, S_2, S_5
1002	Name 2	P_4	S_1, S_3
1003	Name 3	P_2, P_3	S_1, S_2
1004	Name 4	P_1	S_2

This is just way to complicated --- and it must be so slow. Two left joins with distinct solve it better — Hogan, Feb 10 '23 at 15:13
@Hogan Your answer is repeating the same value (my sample data with your answer for ID 1001 results as PRODUCTS = P_1, P_1, P_1 ). You have the same issue with SERVICES too. The quqestion is explicite - no repetings. — d r, Feb 10 '23 at 16:57

score 0 · Answer 2 · answered Feb 10 '23 at 15:11

Since you can't use LISTAGG(DISTINCT... in Oracle (you can in DB and SQL Server) your query you can do the following to solve the issue of multiple items in the list:

SELECT A.ID, A.NAME,
 LISTAGG(P.PRODUCTID, ', ') WITHIN GROUP (ORDER BY P.PRODUCTID) "PRODUCTS",
 LISTAGG(S.SERVICEID, ', ') WITHIN GROUP (ORDER BY S.SERVICEID) "SERVICES"
FROM TABLE_A A 
LEFT JOIN (
  SELECT DISTINCT ID, PRODUCTID
  FROM TABLE_B
) AS P ON A.ID = P.ID
LEFT JOIN (
  SELECT DISTINCT ID, SERVICEID
  FROM TABLE_B
) AS S ON A.ID = S.ID 
WHERE some conditions
GROUP BY A.ID, A.NAME;

Left Outer JOIN with no multiple records of left table

2 Answers2