Page 1 of 1

Simple join returning odd results

Posted: Thu Mar 11, 2004 10:47 pm
by decoy1
I have a very simple database set up where I use one table to display all the current link categories available, and another to hold the respective links. 'catid' is the primary key for the 'link_cat' table and the foreign key to the 'links' table.

Everything works well, but on an administrative section of the site, I give the owner the ability to add/edit/delete the categories and links. I simply loop through both tables and list all the categories with an 'edit' and 'delete' button next to it and a seperate table for the 'links' table.

This too works well, but I'd like to isolate the categories that exist, but don't have any links associated with them yet, and stray links lying around after the category they were once associated with no longer exist.

LINK_CAT TABLE
---------------
catid <-- pri key
cat_name

LINKS TABLE
-----------
linkid <-- pri key
catid <-- foreign key
link_url
alt

When I use this query, it comes back correct. It returns the link(s) which have an existing catid in the 'link_cat' table

Code: Select all

SELECT name FROM links, link_cat
WHERE links.catid = link_cat.catid;
However if I try this one, it returns everything twice, PLUS it also returns the link that has a matching catid in the 'link_cat' table??

Code: Select all

SELECT name FROM links, link_cat
WHERE links.catid != link_cat.catid;
The only difference in them is the '!' in front of the '=' on the second query. I'm not even sure if I'm going at this the right way. I appreciate all suggestions.

Thanks :)

Posted: Sat Mar 13, 2004 9:31 am
by Weirdan
If you need to select the links which have no corresponding category use LEFT JOIN:

Code: Select all

select name from links left join link_cat on links.catid=link_cat.catid where link_cat.catid is null