CODEWITHSUNDEEP
mysqljoin
user1041931
user1041931

Reputation: 117

MySQL: combining 2 JOINS

Having a problem combining 2 JOINS. Both statements below work. I have also included the relevant fields in the 2 DBs. My question is: 1) How do I combine the 2 statements into 1 statement? 2) How do I reference the rows in my php to distinguish if the result was from the mmpreID JOIN or the mmparID JOIN?

Thanks

DB: matchmaker
mmpreID
mmparID

DB: users
uID
ufname
ulname

SELECT matchmaker.mmpreID, matchmaker.mmparID, users.uID, users.ufname, users.ulname 
FROM matchmaker 
LEFT JOIN users ON matchmaker.mmparID = users.uID

SELECT matchmaker.mmpreID, matchmaker.mmparID, users.uID, users.ufname, users.ulname 
FROM matchmaker 
LEFT JOIN users ON matchmaker.mmpreID = users.uID

Upvotes: 2

Views: 1939

Answers (2)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

SELECT m.mmpreID, m.mmparID, u.uID, u.ufname, u.ulname,
    case 
        when m.mmparID = u.uID then 'mmparID'
        when m.mmpreID = u.uID then 'mmpreID'
        else null 
    end as Source
FROM matchmaker m
LEFT JOIN users u ON m.mmparID = u.uID or m.mmpreID = u.uID

Update:

SELECT m.mmpreID, m.mmparID,
    upar.uID as upar_UID, 
    upar.ufname as upar_UFNAME, 
    upar.ulname as upar_ULNAME,
    upre.uID as upre_UID, 
    upre.ufname as upre_UFNAME, 
    upre.ulname as upre_ULNAME
FROM matchmaker m
LEFT JOIN users upar ON m.mmparID = upar.uID 
LEFT JOIN users upre ON m.mmpreID = upre.uID

Upvotes: 5

Alexey Gerasimov
Alexey Gerasimov

Reputation: 2141

Add UNION and another column to see where it came from

SELECT matchmaker.mmpreID, matchmaker.mmparID, users.uID, 
users.ufname, users.ulname, "mmpar only" as source
FROM matchmaker 
LEFT JOIN users ON matchmaker.mmparID = users.uID AND matchmaker.mmparID != matchmaker.mmpreID 

UNION

SELECT matchmaker.mmpreID, matchmaker.mmparID, users.uID, 
users.ufname, users.ulname, "mmpre only" as source
FROM matchmaker 
LEFT JOIN users ON matchmaker.mmpreID = users.uID
AND matchmaker.mmparID != matchmaker.mmpreID 

UNION

SELECT matchmaker.mmpreID, matchmaker.mmparID, users.uID, 
users.ufname, users.ulname, "both" as source
FROM matchmaker 
LEFT JOIN users ON matchmaker.mmpreID = users.uID  AND matchmaker.mmparID = matchmaker.mmpreID

Revised the answer above to include 3 possible outcomes 1. uID only matches mmparID 2. uID only matches mmpreID 3. uID matches both mmparID and mmpreID

Yes, UNIONS are not very efficient but they are necessary at times

Upvotes: -1

Related Questions