Too many results from query

[yowza]

I don't think there is a SQL forum on WW, so I posted here.

I'm trying to return all rows from one table1 that don't have a matching record in table2. This was very easy to do.

The problem is that if all records from table1 have a match in table 2, all rows are returned when no results should be returned.

I've tried forever to figure this out, but have had no luck.

Here's the SQL:

SELECT books.title,books.category,books.prodid FROM books,categories WHERE books.category!= categories.catid

If there is one record in books.category that is not in categories.catid, it returns the results that I want. However, if they both have matching records, it returns all results.

Thanks.

[ergophobe]

see message #7 this thread:

[webmasterworld.com...]

Tom

[yowza]

I didn't really see anything in that thread.

I've already tried an inner join with the same result as above.

[coopster]

I think ergophobe meant for you to use a

LEFT JOIN

.

If there is no matching record for the right table in the

ON

or

USING

part in a LEFT JOIN [mysql.com], a row with all columns set to

NULL

is used for the right table. You can use this fact to find records in a table that have no counterpart in another table:

SELECT 
 books.title, 
 books.category, 
 books.prodid 
 FROM books 
 LEFT JOIN categories 
 ON books.category = categories.catid 
 WHERE categories.catid IS NULL  
;

[yowza]

Beautiful!

I never took "categories" out of FROM. That was my only problem when I tried the JOIN before.

Thanks!