CODEWITHSUNDEEP
mysqlsql
Daniel Li
Daniel Li

Reputation: 397

Subscriber Dependents table mysql

I have two tables Subscriber and Dependents

 Subscribers
 1 John
 2 Joe
 3 Jean
 4 Alan
 5 Elena

 Dependents
 1 John (Same Person)
 1 Betty
 1 Hannah
 3 Grey
 4 Sumen
 4 Kevin

SELECT * FROM Subscribers LEFT JOIN Dependents ON Subscribers.Id = Dependents.Id

This gives me

John, John
John, Betty
John, Hannah
Joe,
Jean, Grey
Alan, Sumen
Alan, Kevin
Elena

I would need:

John, John
John, Betty
John, Hannah
Joe
Jean (Added)
Jean, Grey
Alan (Added)
Alan, Sumen
Alan, Kevin
Elena

Basically I want the subscriber record to be independent on themselves if the dependent isn't themselves like John. Joe, Jean, Alan, Elena will all have records not related to the dependents.

I would think I need something like this

 SELECT * FROM SUBSCRIBERS LEFT JOIN Dependents ON Subscribers.Id = Depedents.Id
 Where Subscribers.First_Name IS NOT Dependents.First_Name

Thanks!

Upvotes: 0

Views: 186

Answers (2)

Andriy M
Andriy M

Reputation: 77707

You could also use a UNION ALL like this:

SELECT s.Name AS subscriber, d.Name AS dependent
FROM Subscribers AS s
INNER JOIN Dependents AS d ON s.Id = d.Id

UNION ALL

SELECT s.Name, NULL
FROM Subscribers AS s
LEFT JOIN Dependents AS d ON s.Name = d.Name
WHERE d.Name IS NULL

ORDER BY subscriber, dependent
;

Basically, the first SELECT gets all the matches by Id, while the second one adds subscribers that have names not found in dependents.

Upvotes: 0

Barbara Laird
Barbara Laird

Reputation: 12717

I couldn't think of any way to do this without a union. 

SELECT S.name as subscriber, D.name as dependent FROM Subscribers S left JOIN Dependents D ON S.Id = D.Id
    UNION 
    SELECT S.name as subscriber, D.name as dependent FROM Subscribers S left JOIN Dependents D ON S.name = D.name
    ORDER BY subscriber, dependent

http://sqlfiddle.com/#!2/dc7ba/11

Upvotes: 1

Related Questions