1

Table A has ID and Name fields and the following data
1 Joe
2 John
2 John
2 John
4 George
4 George
4 George

Table B has ID and a different field
1 ABC
1 XYZ
3 ABC
4 ABC
4 GHI
4 XYZ

I would like to have a query that would return only one record from Table A, if there, but will have multiple rows if Table B contains multiples.
I would also like to have an indicator that there was no joining record (Null)
Desired results:
1 Joe ABC
1 Joe XYX
2 John Null
3 Null ABC
4 George ABC
4 George GHI
4 George XYZ

Characteristics:
Brings back Joe from Table A twice because there are 2 rows in Table B with same ID
Brings back John from Table A once even though there are no rows in Table B to indicate the same
Brings back ID 3 from Table B once even though there are no rows in Table A to indicate the same
Brings back George from Table A three times because there are 3 rows in Table B with same ID

Thank you

2
  • Please consider following these suggestions.
    – mustaccio
    Commented Sep 20, 2022 at 19:12
  • Join by ID and apply DISTINCT.
    – Akina
    Commented Sep 21, 2022 at 4:37

1 Answer 1

0
SELECT DISTINCT ID, A.name, B.diff
    FROM A
    LEFT JOIN B  USING(ID);

Consider also

SELECT ID, A.name,
       GROUP_CONCAT(B.diff) AS 'other stuff'
    FROM A
    LEFT JOIN B  USING(ID);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.