SQL Querying in multiple tables

Question

I have two tables, customers and Orders

Customers columns are

CustomerID,
Username,
Password,
Firstname,
Surname,
Email,
Mobile

Orders columns are

OrderID,
CustomerID,
Date,
Time,
Price,
Complete

I want to select all of the firstname and surnames from all the orders that have been complete. And yes It could be so that [0] = John Smith and [1] also = John Smith.

What I was thinking was

SELECT FirstName, Surname from order, customers
WHERE Complete = 'Yes' AND order.CustomerID = customer.CustomerID;

So first it looks at if the order is complete. If it is then it will look at the customer ID, then it will go to customers and get the firstname and surname of that customer then store it in the datatable.

Thank you for any help!!!

Tip: Store date and time as a single entity

Strawberry
– Strawberry

2017-02-15 16:18:15 +00:00
Commented Feb 15, 2017 at 16:18 — Strawberry
– Strawberry, Commented Feb 15, 2017 at 16:18

fthiella · Accepted Answer · 2017-02-15 15:56:15Z

2

You could use EXISTS, the following query will return all customers that don't have incomplete (=0) orders:

select c.firstname, c.lastname
from customers c
where
  not exists (select * from orders o
              where c.customerid = o.orderid
                    and o.complete = 'No')

but it will also return customers that have no orders. If you want to exclude customers with no orders you could use an additional exist clause:

select c.firstname, c.lastname
from customers c
where
  not exists (select * from orders o
              where c.customerid = o.orderid
                    and o.complete = 'No')
  and exists (select * from orders o where c.customerid = o.orderid)

or a group by clause:

select c.firstname, c.lastname
from customers c inner join orders o on c.customerid = o.customerid
group by c.customerid, c.firstname, c.lastname
having sum(o.complete='No') = 0

answered Feb 15, 2017 at 15:56

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Over a year ago

Group by wouldn't work, since OP wants duplicates if multiple orders have the same customer.

r0xette · Accepted Answer · 2017-02-15 15:58:06Z

0

This will give you list of Firstname, Surname even though they don't have orders.

SELECT Customers.Firstname, Customer.Surname
FROM Customers, Orders
WHERE Orders.Complete = 'Yes'
LEFT JOIN Customers.CustomerID = Orders.CustomerID

answered Feb 15, 2017 at 15:58

r0xette

9083 gold badges12 silver badges26 bronze badges

1 Comment

Strawberry Over a year ago

Who upvotes this stuff? You cannot simply make up syntax.

Andrew · Accepted Answer · 2017-02-15 16:44:12Z

0

I'd go with this personally:

SELECT c.Firstname, c.Surname FROM Customers c
INNER JOIN Orders o
ON c.CustomerID=o.CustomerID
WHERE o.Complete='Yes'

I like to be as explicit as possible with my queries, so anyone else who has to read my code understands the what, why and how. Though shouldn't you also select something to identify the order? Otherwise all you have is a list of names.

edited Feb 15, 2017 at 16:44

answered Feb 15, 2017 at 16:39

Andrew

1,6121 gold badge24 silver badges40 bronze badges

Comments

user1327961 · Accepted Answer · 2017-02-15 18:10:52Z

0

--Unique list of customer id, customer first name and customer surname.
SELECT DISTINCT
    customers.customerid
    , customers.firstname
    , customers.surname
FROM        orders
INNER JOIN  customers 
ON 
    customers.customerid = orders.customerid
    AND orders.complete = 'Yes'

--Unique list of customer first name and customer surname, regardless 
--if same names are tied to different customerid.
SELECT DISTINCT
    customers.customerid
    , customers.firstname
    , customers.surname
FROM        orders
INNER JOIN  customers 
ON 
    customers.customerid = orders.customerid
    AND orders.complete = 'Yes'

Remove DISTINCT word if you want duplicates.

answered Feb 15, 2017 at 18:10

user1327961

4522 silver badges8 bronze badges

Collectives™ on Stack Overflow

SQL Querying in multiple tables

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related