SQL select query...?

Question

I have the following tables:

categories (category_ID, category)
stories_category (story_ID, category_ID)

I want to return all (category) from categories which do NOT have (category_ID) in stories_category for a given (story_ID)...

Any help? :) I was up til 6am trying to think this one through...

Answer 1

It's hard to test without an example database but wouldn't the following do the trick?

SELECT category FROM categories WHERE category_ID NOT IN (SELECT category_ID FROM stories_category WHERE story_ID=given_story_ID);


####
NOTE that you asked for all (category) from categories (i.e., only one row); the next two answers return EVERYTHING from categories (i.e., all rows). Hence, these answers are technically incorrect. :-)
####

Answer 2

SELECT * FROM categories
WHERE category_ID NOT IN (SELECT category_id from stories_category WHERE story_ID = given_id)

Answer 3

This will return any unused categoryID's. (Those CatID's not used in the table Story.

SELECT Category.CatId, Category.Category
FROM Category
LEFT JOIN Story ON Category.CatId = Story.CatD
WHERE (((T_Story.CatID) Is Null));

Answer 4

This syntax should work for all versions of SQL, including MySQL versions prior to 4.2 (which don't support subqueries or inner joins; all the previous suggestions use subqueries). Just change VALUE to be the story_id key you want.

SELECT c.category
FROM categories c
LEFT JOIN stories_categories s
ON s.category_id = c.category_id
WHERE s.story_id = 'VALUE'
AND s.category_id IS NOT NULL

Answer 5

hmmm... this looks like a homework question, but here goes

select * from categories c
where c.category_ID not in (
select s.category_ID from stories_category s
where story_ID='a given story'
);

untested.