Simple join returning odd results
Posted: Thu Mar 11, 2004 10:47 pm
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
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??
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
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;Code: Select all
SELECT name FROM links, link_cat
WHERE links.catid != link_cat.catid;Thanks