Joining and stuff :P

DanielLiljeberg

"Normal" queries is no problem for me but I find it that they can be more effective if I learn to understand join and subqueries etc. So here is on thing I'm trying to figure out.

I have two tables. forum_threads and news
Now, the thing is that when you post a news a thread is also automaticly created to carry the discussions of that news. I COULD insert a is_thread field in forum_threads, but that doesn't seem nice. Want the forum to not have any physical connections to the newssystem so I can eassily extract the forum and use it in other sites. So I would like to do something like this:
select * from forum_threads (WICH ID DOES NOT EXIST IN FIELD dorum_thread OF NEWS TABLE) WHERE is_topic = 1.
If u didn't figure it out it's the part in the ()'s I'm not so sure about 😉

Crudely they look like this.

forum_threads
id
is_topic
header
body
poster
time

news
id
header
body
poster
forum_thread

So, any ideas of a nice join or subquery or whatever. And also.. any good sites that explain these things in an easy fashion. mySQL dosumentation is so cluttered that I fall asleep before I begin to read 😛

Thanx in advance guys.

devinemke

SELECT * FROM forum_threads WHERE id NOT IN(SELECT forum_thread FROM news WHERE forum_thread IS NOT NULL)

this assumes that the field "forum_thread" in the news table defaults to NULL when it is not linked to a thread. this also assumes that you are using mySQL version 4.1 or higher.