The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

SQL query...

Having a bit of trouble with the SQL query/logic. It *should* be simple

I have a table of articles and a table of related articles. Now i am calling a "edit related articles page" and what it should do is list all the articles with a checkbox next to them. Ok, that is all fine. What i want however is for the checkbox to be selected if there is this article is already marked as "related."

The tables are:

articles (where name, id) are needed
and then
related_articles

where it has

show_article
related_article

So at the moment i am just doing

$db->query("SELECT title,id FROM article WHERE id != '$article_id'");

and looping through.

but what i need is some sort of JOIN (i think) that checks the other table first. I am not getting far at this, all the joins I am doing are resulting in the list of the articles more than once if they are also related to other articles (ie the ones we are not dealing with).

The only easy solution for me at the moment is just doing a 2nd SQL query on each article returned but that is far from optimim.

p.s. what i want in the results i just a field that i can do some validation on that says "this article is already related to this article". Then i just do an if/else display of a checkbox that is checked or not checked.

now the idea is the add relations page lists every article but knows already if there is corresponding entry match for show_entry (ie the one we are adding relations to) for that related entry (hence the already matched checkbox).

This new construct says ‘give me article IDs along with their corresponding related articles if they have any’. Maybe that is not the best way to write it but what MySQL will do is grab EVERY article – whether or not it has a related article – and display them to you.

“Then, what is the contents of ‘related_article’ for an ID without one”, you ask? Well, most RDBMS’ will use ‘NULL’. I suspect MySQL does the same thing.

So, your application should check for NULL in related_article and if so, then there are no related articles. If there is a non-null ID, then it *is* a related article.