Join Help

[Stressed]

OK I need help with a query. Lets say I have the following tables:

content table

contentID,
contentName,
.. more columns ..

site_content table
siteID
contentID

So in content table we would have something like

1 - Content 1
2 - Content 2
3 - Content 3

In the site_conten ttable I would have

site1 - 1
site 2 - 1
site 3 - 1
site 1 - 2

So lets say I want to find all the items in the content table that are not associated to a specific site in site_content.

Ie., I want to find the content that is not assigned to site 2.

So in this instance it would return 2 & 3 from the content table because content #1 is assigned to site 2.

Hope this makes sense, im not good at complex joins at all. I think I need some sort of self join but have no idea how to do it properly.

Please advise.

Thanks.

[AstroTeg]

SELECT contentid FROM content left join site_content ON site_content.contentid = content.contentid WHERE site_content.siteid IS NULL

Untested, but I think the theory will work. Basically, you'll want to join the two tables together, but you don't want to use an INNER JOIN. An inner join will link only those IDs that match. A left join on the other hand links all IDs it can find. If it can't find an ID, it still brings back the data (in this case, it brings the data base from the content table). So, you want to use a left join and you want to look for content IDs where they don't have a site ID. So the WHERE clause is where we request rows of data where the site ID is null (or doesn't exist basically).

[ultraslacker]

To understand joins, I would recommend that you start with two or three tables with less than ten rows each and select columns from each with different combinations of JOINs and WHEREs.

Here's a good sql tutorial site with a few sections on JOINs.