Retrieving Problem

[lead2gold]

This works great and gets me all the parks with a specific season id (in this case id = 1)
[code]
SELECT park.*,s.*,files.rin as f_rin,files.file_desc,google_map.rin AS gmap_rin FROM park
LEFT JOIN files ON files.rin = park.p_rin
LEFT JOIN google_map ON google_map.ref_rin = park.pid AND google_map.ref_rtype = 'PP'
INNER JOIN park2season AS ps ON (ps.pid = park.pid AND ps.sid = '1' )
LEFT JOIN season AS s ON s.sid = '1' ORDER BY s.year DESC,name
[/code]

And this code works great to retrieve absolutly everything
[code]
SELECT park.*,s.year,s.sid,files.rin as f_rin,files.file_desc,google_map.rin AS gmap_rin FROM park
LEFT JOIN files ON files.rin = park.p_rin
LEFT JOIN google_map ON google_map.ref_rin = park.pid AND google_map.ref_rtype = 'PP'
LEFT JOIN season AS s ON s.year = (SELECT MAX(s2.year) FROM season AS s2,park2season AS ps WHERE s2.sid = ps.sid AND ps.pid = park.pid)
ORDER BY s.year DESC,name
[/code]

What i need is a select that will get me everything that is NOT in the park2season table..
thus a park that is not associated with any season at all....(not sure if that makes sense)

It would be something like this i think... but i dont' know the logic..
[code]
SELECT park.*,files.rin as f_rin,files.file_desc,google_map.rin AS gmap_rin FROM park
LEFT JOIN files ON files.rin = park.p_rin
LEFT JOIN google_map ON google_map.ref_rin = park.pid AND google_map.ref_rtype = 'PP'

/* this line below how do i rework it logically */
WHERE park.pid IS NOT IN park2season AS ps ON (ps.pid = park.pid  )
[/code]

Basically there will not be an entry in the park2season table at all for this select...

[fenway]

For the table with the missing entry, you need to do a LEFT JOIN and then check IS NULL on any non-null field in that table (e.g uid / FK).

[lead2gold]

[!--quoteo(post=384741:date=Jun 16 2006, 04:36 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 16 2006, 04:36 PM) [snapback]384741[/snapback][/div][div class=\'quotemain\'][!--quotec--]
For the table with the missing entry, you need to do a LEFT JOIN and then check IS NULL on any non-null field in that table (e.g uid / FK).
[/quote]

Thanks for the reply fenway!

I have 3 tables (but only 2 are important)

TABLE park2season{
pid BIGINT(20), -- this is the park id
sid BIGINT(20), -- this is the season id
}


TABLE park{
pid BIGINT(20); -- park id
}

There can be many parks, but only some of them are set up for the season.... Thus... only some have an entry in park2season linked by the pid.

My question above (worded way to heavily is basically asking...)
How do i only select the items from the park table that are NOT in the table park2season?

Chris

[fenway]

If the "matching" parks2season record is missing, then a "WHERE parks2season.pid IS NULL" in the left join query you describe should suffice.

Hope that helps.

[lead2gold]

[!--quoteo(post=384870:date=Jun 16 2006, 09:57 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jun 16 2006, 09:57 PM) [snapback]384870[/snapback][/div][div class=\'quotemain\'][!--quotec--]
If the "matching" parks2season record is missing, then a "WHERE parks2season.pid IS NULL" in the left join query you describe should suffice.

Hope that helps.
[/quote]

Sorry, thats what you had said the first time...i didn't clue in!
That worked perfectly! thank you very much!