user1814734
user1814734

Reputation:

Join Multiple Table on Mysql

I've bellow tables:

Services

╔════╦═════════════╗
║ ID ║ DESCRIPTION ║
╠════╬═════════════╣
║  1 ║ A           ║
║  2 ║ B           ║
║  3 ║ C           ║
╚════╩═════════════╝

Staff

╔════╦══════╗
║ ID ║ NAME ║
╠════╬══════╣
║  1 ║ ABC  ║
║  2 ║ DEF  ║
║  3 ║ GHI  ║
╚════╩══════╝

StaffServices

╔══════════╦═════════════╦═════╗
║ STAFF_ID ║ SERVICES_ID ║ QTY ║
╠══════════╬═════════════╬═════╣
║        1 ║           1 ║   2 ║
║        1 ║           3 ║   3 ║
║        2 ║           3 ║   1 ║
║        3 ║           2 ║   4 ║
╚══════════╩═════════════╩═════╝

How to query all those tables to get result as bellow?

Result:

╔══════╦═════════════╦════════╗
║ NAME ║ DESCRIPTION ║  QTY   ║
╠══════╬═════════════╬════════╣
║ ABC  ║ A           ║ 2      ║
║ ABC  ║ B           ║ (null) ║
║ ABC  ║ C           ║ 3      ║
║ DEF  ║ A           ║ (null) ║
║ DEF  ║ B           ║ (null) ║
║ DEF  ║ C           ║ 1      ║
║ GHI  ║ A           ║ (null) ║
║ GHI  ║ B           ║ 4      ║
║ GHI  ║ C           ║ (null) ║
╚══════╩═════════════╩════════╝

Thank you for your help

Upvotes: 2

Views: 98

Answers (1)

John Woo
John Woo

Reputation: 263693

SELECT  a.Name, b.Description, c.Qty
FROM    Staff a
        CROSS JOIN Services b
        LEFT JOIN StaffServices c
            ON a.ID = c.Staff_ID AND
                b.ID = c.Services_ID
ORDER   BY a.Name, b.Description

Upvotes: 1

Related Questions