Home > Back-end >  Select all customers and all orders with single query
Select all customers and all orders with single query

Time:11-02

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║
╠═══════╬══════════╬═══════╣
║ 11 ║  wait ║
║ 21 ║  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 ║             ║             ║
╚════╩═════════════╩═════════════╩═════════════╝

CodePudding user response:

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;
  • Related