[Resolved] MySQL query woes...

djwiltsh

Hi everyone

I am trying to create a query that will display only the people who are available on a given date. So I have a table full of people, and a table in which I record when they are not available.

My tables are:

person : personID, personName, personType
personType: personTypeID, personTypeName
unavailable: unavailableID, unavailablePersonID, unavailableDate

My query is:

SELECT personName, personID, unavailablePersonID, unavailableDate, personType, personTypeName, personTypeID
FROM person
LEFT JOIN unavailable ON unavailableDate = '2004-12-12'
LEFT JOIN personType ON person.personType = personType.personTypeID
WHERE person.personID != unavailable.unavailablePersonID
AND personTypeName = 'Musician'
LIMIT 0 , 30

That works fine as there is an entry in the unavailable table for the 2004-12-12.

However, when I change the date to one in which there are no records in the unavailable table, I get no results instead of a list of everyone!

Help!

Thanks

Daniel

tomhath

I didn't test it, but try moving this part into the ON clause of the left join. The way you have it in the WHERE clause it will apply to all rows returned from the joins (and obviously cause them to be eliminated from the result if no row was matched in unavailable).

person.personID != unavailable.unavailablePersonID

djwiltsh

I tried what you suggested and changed the SQL to:

SELECT personName, personID, unavailablePersonID, unavailableDate, personType, personTypeName, personTypeID
FROM person
LEFT JOIN unavailable ON unavailableDate = '2004-12-14'
AND person.personID != unavailable.unavailablePersonID
LEFT JOIN personType ON person.personType = personType.personTypeID
WHERE person.personID != unavailable.unavailablePersonID
AND personTypeName = 'Musician'
LIMIT 0 , 30

However, it still returns no people instead of all people when there is no entry in the unavailable table for the specified date....

Daniel

tomhath

Move means move, not copy :bemused:

SELECT personName, personID, unavailablePersonID, unavailableDate, personType, personTypeName, personTypeID
FROM person
LEFT JOIN unavailable ON unavailableDate = '2004-12-14'
AND person.personID != unavailable.unavailablePersonID
LEFT JOIN personType ON person.personType = personType.personTypeID
WHERE  personTypeName = 'Musician'
LIMIT 0 , 30

djwiltsh

Sorry, my mistake, I was trying it late last night and wasn't too awake!

OK, that now works perfectly on a date when there is no entry in the unavailable table, I get a list of all people.

However, for example, I have person 1 being unavailable on 2004-12-12, and if I change it to that date, person1 is still listed!

So nearly there... Any more ideas?

Thanks

Daniel

tomhath

I didn't look that closely at the query the first time. You need two changes, match the unavailable date AND person, are equal, then in the WHERE make sure no row was found:

SELECT personName, personID, 
       unavailablePersonID, unavailableDate, 
       personType, personTypeName, personTypeID
FROM person
LEFT JOIN unavailable 
ON   unavailableDate = '2004-12-14'
 AND person.personID = unavailable.unavailablePersonID
LEFT JOIN personType 
ON   person.personType = personType.personTypeID
WHERE  personTypeName = 'Musician'
  and unavailable.unavailablePersonID IS NULL
LIMIT 0 , 30

djwiltsh

YEY!

Thank you, that has totally fixed the problem.

Thanks for your time.

Daniel