[SOLVED] query using 2 tables

[mkybell]

ok, this is probably pretty simple. I've spent hours and can't get this.

 

I have two tables, one has items being reviewed and the other has the results of the review forms. They're tied together with item IDs used in both. The review table also lists the reviewer. So, basically the item table will list an item with a unique item ID and other fields with item information. A review table record will have the unique item ID, a unique ID for the reviewer, and all the rest of the review info. This table holds all item reviews posted from various reviewers.

 

When a reviewer is logged in I need them to see a list of items that they haven't yet reviewed.

I was very close to getting this using JOIN. I got all items that a particular reviewer hadn't reviewed but also got items that they had if there was a record in the database from someone else having reviewed it.

 

Would someone please help me with this?

Figure I have a table called 'items' and one called 'reviews'.

Both tables have fields labeled 'itemID' and the 'reviews' table has a field called 'reviewerID'.

 

Thanks in advance.

[btherl]

Nice use of highlighting

 

Hmm.. tough situation.  How about

 

SELECT *
FROM items
WHERE itemID NOT IN
  (SELECT itemID FROM reviews WHERE reviewerID = $reviewer)

 

The subquery gets ids for all items reviewed by that reviewer.  Then the outer query gets all items NOT in the list of items reviewed by that reviewer.

[mkybell]

that makes sense but returned all records

 

this is the closest thing that works

 

SELECT *

FROM items

LEFT JOIN reviews

ON items.itemID=reviews.itemID

WHERE review.reviewerID != $reviewer

 

this way almost works but I also get items that have been reviewed but by someone else, so they still exist in the review table

 

if I just change the !=, on the last line, to = then I get items that have already been reviewed by that reviewer. all I need here is to get every other item record but can't figure it out

[fenway]

Why not just a LEFT JOIN... IS NULL?

[mkybell]

I tried your suggestion with review.reviewerID IS NULL and it only returns one record. With the data I'm testing with I should be getting 3. Unless you can be more specific I don't know how else to apply IS NULL.

[fenway]

Don't you want to find all reviewers without reviewed itemws?

[mkybell]

no, after a particular reviewer logs in I want to list all items that haven't yet been reviewed by that person

an item they haven't reviewed might have already been reviewed by someone else

[bwochinski]

I threw some test data into a couple of tables and this seems to work:

SELECT *

FROM items

LEFT JOIN reviews

ON items.itemID=reviews.itemID

AND reviewerID=$reviewer

WHERE reviewerID IS NULL

[btherl]

mkybell, what do you get from the subquery from my above suggestion?

 

SELECT itemID FROM reviews WHERE reviewerID = $reviewer

 

Does this really fetch all reviews by the current reviewer?

 

If it does, then the query I suggested will work.

[mkybell]

bwochinski,

that variation on the LEFT JOIN worked perfectly

thanks a lot 

 

btherl,

the subquery that you listed does work. for some reason the whole query still returns all item records. I don't know  ???

I've read that you can use subqueries to do what you can do with a LEFT JOIN so it must be possible.

[fenway]

I threw some test data into a couple of tables and this seems to work:

SELECT *

FROM items

LEFT JOIN reviews

ON items.itemID=reviews.itemID

AND reviewerID=$reviewer

WHERE reviewerID IS NULL

 

That's what I was getting at.