NiallFH Posted September 25, 2013 Share Posted September 25, 2013 (edited) Good afternoon all. I am using MySQL 5.0 and wish to create a query which will allow me to display the following: I have three tables. One is a database of years. YearID - YearName The second is a database of registrations RegistrationID - RegistrationYear - RegistrationPerson The third is a database of people PersonID - PersonName (I have identified the personid as a variable earlier in my code). Ultimately, what I want to do, is show a list of years that a person is NOT registered to. I have tried a few different variations, but as yet have been unable to put together a query that will do this. I can however display a list of years to which a person IS registered, using this query: $get_registered_years = mysql_query(" SELECT Y.YearName AS yearname FROM lpro_years Y, lpro_registrations R WHERE R.RegisteredYear = Y.YearID AND R.RegisteredPerson = $personid ORDER BY Y.YearName ",$conn) or die(mysql_error()); It baffles me that I cannot do the reverse? Any help would be greatly appreciated. Edited September 25, 2013 by NiallFH Quote Link to comment Share on other sites More sharing options...
Psycho Posted September 25, 2013 Share Posted September 25, 2013 A couple things first - use consistent naming of your database fields. If you have a primary key called 'YearID', then use that same name when using that as a Foreign Key in a different table. That allows you to JOIN tables using the USING() function. And, byt the way, based upon what you are asking the people table is irrelevant to the problem. Since I decided to create the tables to test this, I took the liberty to rename the fields as follows: lpro_people: PersonID, PersonName lpro_years: YearID, YearName lpro_registrations: RegistrationID, PersonID, YearID You can solve this problem in several ways, here are two 1) Do a select on the years table and do a LEFT JOIN on the registration table using the YearID and only records for the specified person. This will create a result set with ALL years included, but where there is no corresponding record in the registration table, those fields would be NULL. So, just add a condition in the WHERE clause to only include those records with NULL for one of the registration fields. SELECTyr.YearName, yr.YearID FROM lpro_years AS yr LEFT JOIN lpro_registrations AS rg ON yr.YearID = rg.YearID AND PersonID = $personID WHERE PersonID IS NULL 2) Do a select query on the years table and add a WHERE condition looking for year IDs that are NOT in the list of year ids of the registration records for the person SELECT yr.YearName, yr.YearID FROM lpro_years AS yr WHERE YearID NOT IN (SELECT YearID FROM lpro_registrations WHERE PersonID = 1) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.