Show the list of all product's names ordered along with first and last names of the customers

Question

I was doing an exercise and I am stuck on it. There are 2 tables:

Customers(id, firstname, lastname, address)
Orders (id, product_name, product_price, date_order DATE, id_customer, amount)

The query:

SELECT Orders.product_name, Customers.firstname, Customers.lastname
FROM Orders INNER JOIN
     Customers
     ON Orders.id_customer=Customers.id
ORDER BY Orders.id;

Expected result:

Show the list of all products' names ordered along with first and last names of the customers.

Include in the result only those customers who have no address in a database and sort the data by Orders.id.

i assume this is a homework/job interview related question? Read [How do I ask and answer homework questions?](https://meta.stackoverflow.com/questions/334822/how-do-i-ask-and-answer-homework-questions) .. Also see [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Raymond Nijland, Aug 15 '19 at 17:49

score 2 · Accepted Answer · answered Aug 15 '19 at 17:42

Looks like you're pretty close. You just need a WHERE clause to match this requirement:

Include to the result only those customers who has no address in a database

Try this:

SELECT Orders.product_name, Customers.firstname, Customers.lastname
FROM Orders INNER JOIN Customers ON Orders.id_customer = Customers.id
WHERE address IS NULL OR address = ''
ORDER BY Orders.id;

score 0 · Answer 2 · answered Aug 15 '19 at 17:44

Add one more condition of address as null

   SELECT Orders.product_name, 
     Customers.firstname, 
    Customers.lastname FROM Orders 
    INNER JOIN Customers ON 
    Orders.id_customer=Customers.id 
    and  Customers.address IS NULL
    ORDER BY Orders.id;

Show the list of all product's names ordered along with first and last names of the customers

2 Answers2