CODEWITHSUNDEEP
phpmysqlmysqli
Photographer Britt
Photographer Britt

Reputation: 115

Checking two separate tables against eachother - Mysqli/PHP

I have two tables, one which contains user information (like a Customer ID, Name, Address, etc) which is called 'Customers' and a second table with appointment information (such as the Customer ID to link it, the date, time, etc) called 'Appointments'. For this I'm trying to search the 'Customers' table but only bring back people who are not scheduled an appointment, which is basically Customers who don't have an entry in the 'Appointments' table. But the generated search I have is timing out the mysqli and crashes it temporarily with a Gateway Time-out.

My code: 

    SELECT Cus.* FROM Customers Cus
WHERE Cus.School='FTHL' AND NOT EXISTS (
SELECT CustomerID FROM Appointments WHERE Cus.CustomerID = CustomerID)
 ORDER BY Cus.CustomerID ASC

I'm using the Cus. prefix as since this sql search is built based on the options checked by the user in the search form and if they were to, for example, search for customers who do have appointments, it can bring in the Appointment table information to the search along with the user's information from the Customer table. That works just fine, it's just this trying to see who DOESN'T have an appointment that's not working.

The two tables look a bit like this:

Customers ------------------------------------
CustomerID, First Name, Last Name, School, etc

Appointments ---------------------------------
CustomerID, Date, Time, etc

Upvotes: 0

Views: 44

Answers (1)

BWS
BWS

Reputation: 3846

try something like this:

SELECT Cus.* 
FROM Customers Cus
WHERE Cus.School='FTHL' 
AND Cus.CustomerID NOT IN (SELECT CustomerID FROM Appointments)
ORDER BY Cus.CustomerID ASC

Upvotes: 2

Related Questions