I am trying to create a script where employees can place orders for customers. Due to the nature of the business, 2 different employees may place an order for the same customer. I would like to print a report so these orders can be verified.
I have a customer table, and an order table, and am attempting to create a query that will list all records where there is a duplicate last name in customers table, but only if their is an order for that last name, (it is possible for a customer to exist without an order).
Either this is an obscure thing to do, or I am not searching for it correctly, as I haven't found any query that accomplishes this.
Thank you for your assistance.
customers table - id, first_name, last_name, address, city, state, zip, phone
orders table - id, customer_id, quantity (there is only 1 product)
Example - there are 4 customers with the last name Johnson, and 2 records with a last name of Johnson also have orders (only the last name needs to match and is the duplicate criteria). I would like the query to print all instances of customers with the last name Johnson, (all fields) where customers.id appears in orders.customer_id
You can use a self join in order to identify duplicate last names. Something sort of like this might suit your purposes:
select c1.* from customers c1
join customers c2 on c1.last_name = c2.last_name and c1.id <> c2.id
join orders o on o.customer_id = c1.id