I have 3 tables, below are a list of the significant fields in each.
Stories
story_id
story_date
Editions
edition_id
publication_id
Story_Edition_Jct
story_id
edition_id
What I need to do is create a list of all the stories that are newer than 30 days and have not been assigned to a given publication_id, but may or may not have been assigned an edition.
Just to clear that up some more, a publication consists of many editions, so we're looking for stories that may have been published in other publications, but want to restrict them from being republished in a given publication.
The following query is what I've come up with:
SELECT * FROM stories,
editions,
story_edition_jct
WHERE stories.story_id = story_edition_jct.story_id AND
editions.edition_id = story_edition_jct.edition_id AND
editions.publication_id != $pubID AND
DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= stories.story_date;
That query works, but because only stories that have already been assigned to an edition have records in STORY_EDITION_JCT table, I can't find all the stories that are newer than 30 days but NOT assigned to an edition. I think there may be a way to do this with a left join, but i'm not really that familar.
If it helps to know, I'm using MySQL 3.23
Any help would be greatly appreciated.
Thanks in advance!