I am having some trouble coming up with a way to extract the data needed from the database. I attached a diagram of the three tables in question.
Basically one headline can be assigned to multiple topics, nothing too complicated there. My goal is to select a few headlines by topic, this too is pretty basic using the following query:
SELECT TOP 3 headline.HEADLINE_ID, headline.HEADLINE_TITLE
FROM tbl_CCF_Headlines headline
INNER JOIN tbl_CCF_Headlines_Topics joiner ON headline.HEADLINE_ID = joiner.HEADLINE_ID
INNER JOIN tbl_CCF_Topics topic ON joiner.TOPIC_ID = topic.TOPIC_ID
WHERE (topic.TOPIC_ID = 27)
ORDER BY headline.HEADLINE_DATE DESC
HERE IS THE CATCH:
I need to only return headlines based on the topic assignment if the headline only has one topic assignment
-- OR --
If the headline has multiple assignments only return the headline if the first appearance of the headline in the joiner table is equal to the topic ID.
Does this make any sense to anybody? I am sure I could explain it better, but my brain feels like oatmeal right now and it is getting hard to think.