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

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 that you have read and understand our privacy policy and code of conduct.

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