CODEWITHSUNDEEP
mysqlsql
Thimotty
Thimotty

Reputation: 45

Select all customers and all orders with single query

Is there a way with single mysql query to output all customers and all their orders including customers without orders? I have:

Customers:

╔════╦═════════════╗
║ ID ║    NAME     ║
╠════╬═════════════╣
║  1 ║ John Smith  ║
║  2 ║ Jim Jimmers ║
╚════╩═════════════╝

Orders:

╔═══════╦══════════╦═══════╗
║  id   ║ ClientID ║ Status║
╠═══════╬══════════╬═══════╣
║ 1     ║        1 ║  wait ║
║ 2     ║        1 ║  rdy  ║
╚═══════╩══════════╩═══════╝

How do I select a result like:

╔════╦═════════════╦═════════════╦═════════════╗
║ ID ║    NAME     ║ Order Id    ║ Order Status║
╠════╬═════════════╬═════════════╬═════════════╣
║  1 ║ John Smith  ║           1 ║        wait ║
║  1 ║ John Smith  ║           2 ║        rdy  ║
║  2 ║ Jim Jimmers ║             ║             ║
╚════╩═════════════╩═════════════╩═════════════╝

Upvotes: 0

Views: 717

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Just use a left join between the two tables:

SELECT c.ID, c.NAME, o.id AS OrderId, o.Status AS OrderStatus
FROM Customers c
LEFT JOIN Orders o
    ON o.ClientID = c.ID
ORDER BY c.ID, o.id;

Upvotes: 2

Related Questions