Reputation:
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
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