Need help with three tables, maybe a JOIN?

[NiallFH]

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

[Psycho]

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)