CODEWITHSUNDEEP
mysql
Jack
Jack

Reputation: 787

SQL Query only returning results for first customer

My sql query only returns results for orders that belong to the first customer in the database. Where have I gone wrong?

SELECT orders.id, 
       orders.created_at, 
       orders.customer_id, 
       customers.firstname, 
       customers.surname, 
       customers.email, 
       customers.telephone, 
       customer_addresses.address_1, 
       customer_addresses.address_2, 
       customer_addresses.address_2, 
       customer_addresses.city,
       customer_addresses.county, 
       customer_addresses.post_code 
FROM orders 
INNER JOIN customers ON orders.customer_id = customers.id 
INNER JOIN customer_addresses ON customers.id = customer_addresses.id 
ORDER BY orders.id 
LIMIT 0, 10;

Upvotes: 0

Views: 61

Answers (2)

Anish Chikodi
Anish Chikodi

Reputation: 96

if all the customers have address_id as you mentioned in your reply to @ceyquem's answer, I think you are joining customer_address table on wrong column. It should be

SELECT orders.id, 
       orders.created_at, 
       orders.customer_id, 
       customers.firstname, 
       customers.surname, 
       customers.email, 
       customers.telephone, 
       customer_addresses.address_1, 
       customer_addresses.address_2, 
       customer_addresses.address_2, 
       customer_addresses.city,
       customer_addresses.county, 
       customer_addresses.post_code 
FROM orders 
INNER JOIN customers ON orders.customer_id = customers.id 
INNER JOIN customer_addresses ON customers.address_id = customer_addresses.id 
ORDER BY orders.id 
LIMIT 0, 10;

Hope it works

Upvotes: 1

Xtophe
Xtophe

Reputation: 2277

If all your customers don't have a defined address, it might be the reason, try changing the JOIN operators as follows to test. Also, remove the limit condition at the end to ensure you can see all the result:

SELECT orders.id, 
       orders.created_at, 
       orders.customer_id, 
       customers.firstname, 
       customers.surname, 
       customers.email, 
       customers.telephone, 
       customer_addresses.address_1, 
       customer_addresses.address_2, 
       customer_addresses.address_2, 
       customer_addresses.city,
       customer_addresses.county, 
       customer_addresses.post_code 
FROM orders 
LEFT JOIN customers ON orders.customer_id = customers.id 
LEFT JOIN customer_addresses ON customers.id = customer_addresses.id 
ORDER BY orders.id;

Upvotes: 3

Related Questions