I have an appointment scheduling app that has two tables, clients and appointments. The appointments table has a FK to the clients table.
I'm trying to create a search that allows the users to search for clients with or without appointments. The only way to tell is to do check the FK in the appointments table like so:
select [fields I want] from appointments where clientid = $clientID
It's easy to do the search with bookings, I just detect if they selected only clients with bookings and then I change the query to a join on the appointments table - simple:
select [fields I want] from appointments, clients where clients.clientID = appointments.clientID
But "not booked" is a problem. How do I submit a query that checks for clients who don't have an associated record in the appointments table? I'm kind of looking for something like this:
select [fields I want] from clients where clientID is not in appointments.clientID
Not sure how the query would look exactly but I just want client records that don't have an associated record in the appointments table.
I have a work-around where I just select everything from clients and then when I'm looping through each row I do a query on the appointments table to see if the client has an appointment and if they do I just don't output the row. But that obviously sucks for a number of reasons I'd rather just have one nice clean result set with the records I want.
Any ideas?
Thanks in advance
