I'm new to php and MySQL and started exploring both by programming a custom CMS.
I have a table called news containing lots of news entries which are auto incremented by a primary key news_id:
news (news_id, title, subtitle, ...)
I have another table called news_tags containing tag titles which are auto incremented by a primary key news_tag_id:
news_tags (news_tag_id, news_tag_title)
Because each news entry can be tagged with multiple tag titles and each tag title can describe multiple news entries both tables are many-to-many-relation. So if a news entry gets tagged I store their relations in a third table called news_tags_relations holding rows with news_id and news_tag_id:
news_tags_relations (news_id, news_tag_id)
Now I want to give the user the possibility to choose tags in order to tag a news entry which may or may not have tags already.
So I need to SELECT all tag titles and their title id that are not linked to any news entry at all and also all tag titles that are linked to other news entries but were not already chosen for the news entry the user is working on.
This works perfectly fine if the news entry's news_id is linked to one tag only:
SELECT news_tags.news_tag_title, news_tags_relations.news_id, news_tags.news_tag_id
FROM news_tags
LEFT JOIN news_tags_relations ON news_tags.news_tag_id = news_tags_relations.news_tag_id
WHERE news_tag_title != (
SELECT news_tag_title
FROM news_tags_relations
JOIN news_tags ON news_tags_relations.news_tag_id = news_tags.news_tag_id
WHERE news_tags_relations.news_id = '".$news_id."'
)
But if the picked news_id is linked to two or more tags I get:
1242 - Subquery returns more than 1 row
This makes sense to me because number of tags linked to that news_id is > than 1 and it seems like MySQL can't handle subqueries returning multiple rows.
If the picked news_id is not linked to any tag I get empty result. I tried to fix that with replacing
WHERE news_tag_title != (
with
WHERE news_tag_title IS NULL OR news_tag_title != (
which didn't change the result being 0.
This was closest I could get yet.
How can I modify my Query to make it work for entries that are linked to multiple tags or no tags at all?