CODEWITHSUNDEEP
phpmysql
H. Ferrence
H. Ferrence

Reputation: 8116

Is 1 single query statement possible

For purposes of the example, I have 2 tables - a couples table and a persons table:

Persons Table
ID PERSON
1 Bob
2 Frank
3 Sally
4 Jane

Couples Table
ID HUSBAND WIFE
1  2  3
2  1  4

Can I write a single query statement to select from both tables and have it join in such a way that the query result will produce:

Couple 1 = Frank and Sally
Couple 2 = Bob and Jane

Thanks

Upvotes: 1

Views: 107

Answers (3)

Joe Phillips
Joe Phillips

Reputation: 51120

SELECT 'Couple ' + c.id + ' ' + h.person + ' and ' + w.person
FROM Couples c
JOIN Persons h ON h.id = c.husband
JOIN Persons w ON w.id = c.wife

Upvotes: 0

Spudley
Spudley

Reputation: 168685

Something like this....

select m.id as husband_id, m.person as husband_name, f.id as wife_id, f.person as wife_name
from couples c
inner join persons m on m.id=c.husband
inner join persons f on f.id=c.wife

Upvotes: 2

ceejayoz
ceejayoz

Reputation: 180014

SELECT Couples.ID, Husband.PERSON, Wife.PERSON
  FROM Couples
    INNER JOIN Persons AS Husband ON Couples.HUSBAND=Husband.ID
    INNER JOIN Persons AS Wife ON Couples.WIFE=Wife.ID

Just a note, though - not every marriage is husband/wife these days. Spouse 1 & Spouse 2 might be more future-proof.

Upvotes: 6

Related Questions