CODEWITHSUNDEEP
mysqlsql
user1818517
user1818517

Reputation: 59

How to run two LEFT joins from the same two tables, but to different fields within those tables

i have an information table that has the following fields in it;

id, staffMember, lineManager, description

The staffMember and lineManager fields return integer values that correspond to the id of rows within a users table which has the following columns

id, firstname, surname

I can use the following query to return the info in my information table, substituting the staffMember value for a CONCAT of firstname and surname:

SELECT information.id, 
    CONCAT( users.firstname,  ' ', users.surname ) AS staffMember, 
    information.lineManager, 
    LEFT(information.description,200) As description 
FROM information 
LEFT JOIN users 
    ON ( information.staffMember = users.id ) 
LIMIT 0 , 30"

But what i want to be able to do, is repeat the process that's working on the value of staffMember on lineManager as well in the same query (which i then pass as a json string) - however, i know i can't have two LEFT joins to the same table but equating different fields.

Any help would be gratefully received.

Upvotes: 1

Views: 85

Answers (2)

Keith
Keith

Reputation: 1372

Something like this:

SELECT information.id FROM information 
LEFT JOIN users u ON u.id = information.staffMember
LEFT JOIN users ul ON ul.id = information.lineManager

The letters/words after the table are completely made up by you. They are aliases for the table name that you make up on the fly.

Upvotes: 0

Taryn
Taryn

Reputation: 247860

It sounds like you want this:

SELECT i.id, 
    CONCAT(u1.firstname,  ' ', u1.surname) AS staffMember, 
    CONCAT(u2.firstname,  ' ', u2.surname  AS lineManager, 
    LEFT(i.description,200) As description 
FROM information i
LEFT JOIN users u1
    ON i.staffMember = u1.id 
LEFT JOIN users u2
    on i.lineManager = u2.id
LIMIT 0 , 30

You just perform a LEFT JOIN on the users table twice. Once you will join on the staffMember and the other time you will join on lineManager. By providing a different table alias to the table you can distinguish between the tables and the values.

Of if you want to be clearer:

SELECT i.id, 
    CONCAT(staff.firstname,  ' ', staff.surname) AS staffMember, 
    CONCAT(manager.firstname,  ' ', manager.surname  AS lineManager, 
    LEFT(i.description,200) As description 
FROM information i
LEFT JOIN users staff
    ON i.staffMember = staff.id 
LEFT JOIN users manager
    on i.lineManager = manager.id
LIMIT 0 , 30

Upvotes: 3

Related Questions