Jump to content

Retrieving Problem


lead2gold

Recommended Posts

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...

Link to comment
Share on other sites

[!--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
Link to comment
Share on other sites

[!--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!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.