SQL query to filter a table using two tables

Question

SQL query to filter a table using two tables

Navigation

#1 by (0 votes)

0

I currently have 4 SQL tables in this way:

CustomersTable, RegistrationTable, OrdersTable and OffersTable

I have to write a SELECT statement that lists all the clients in the CustomersTable table (all fields), which contain rows in the RegistrationTable table or rows in the OrdersTable table with status "closed", in the result table no must show duplicate clients.

As you can see, CustomersTable and RegistrationTable have the field in common "customerId", but between CustomersTable and OrdersTable there is no common field. However, there is another table (OffersTable) that has the fields "customerId" and "ID", so that information can be consulted to the client tables and orders respectively. Remember that a client that appears in the Offer table will not necessarily appear in the Order table or simply the status field could not be "closed".

Therefore, when executing the query, the following result will be returned:

In the results table you should not show duplicate clients. I really appreciate the help. Thanks for the time !!

Note - I'm using MySQL

mysql sql php

asked by Kurono K. 22.07.2017 в 17:03

source

1 answer

FTP access via cmd Error synchronizing remote repository on GitHub from Visual Studio

score 0 · Accepted Answer

Try to use the IN statement, I use SQL server but I imagine that MySQL will have the same or similar sentence.

SELECT * FROM customer 
WHERE 
    customerID IN (SELECT  customerID FROM registration) AND 
    customerID IN (SELECT customerID FROM OFFER, ORDER WHERE OFFER.id=order.id AND status='closed')

I hope it helps you.