Neverside

MYSQL: 2 table select?

Here is the setup
person table:
ID
name

product table:
ID
product_name

person_review table:
ID
name_ID
product_ID

Each person has some products to review, say 20. I want to create a php page, that displays the products that they have not reviewed yet.

Something like this, but this isn't working either.
SELECT product
FROM products, person_review
WHERE person_review.person_ID = 1
AND product.ID != person_review.product_ID

basically displaying the product this person has not reviewed yet.

-----
Eventually I would like to be able to do an Admin type search to display all the people, and the products they haven't reviewed yet, but that is a 3 table JOIN, and I don't even know if that is possible.......something like this:

SELECT name, product
FROM products, person, person_review
WHERE product.ID != person_review.product_ID

basically selecting all the products the person has not reviewed. and displaying the persons name and the product in one row.

thanks

Code:

SELECT a.product b.person_review FROM product_table AS a, person_review AS b WHERE b.person_id = 1 AND a.product_id != b.product_id';

Might work

___________________

| Come join the Purephotoshop.net forums

hmmm, that didn't work
I got pretty close with this

Code:

SELECT person_name, product_name
FROM product, person, person_review
WHERE product.ID = person_review.product_ID
AND person.ID = person_review.person_ID

but that was selecting all the products each person reviewed. I want to select the the products the person hasn't reviewed.

This should do it, then:

Code:

SELECT DISTINCT person.person_name,product.product_name
FROM person, person_review,product
WHERE person.ID = person_review.person_ID AND product.ID != person_review.product_ID
ORDER BY person.person_name

It's not a nice query; it'll create gigantic intermediate tables. I couldn't think of another way, though.

Code:

SELECT DISTINCT person.person_name,products.product_name
FROM person, person_review,products
WHERE person.ID = person_review.person_ID
AND products.ID != person_review.product_ID
AND person.ID = 1
ORDER BY person.person_name

I added the second to last line to better sift through my results, and it was working where the people had only reviewed one prodcut, but with the person that reviewed more than one product, it wasn't working properly......any idea why?

Yeah, I'm a moron - I knew there was something I missed...

I've been thinking and thinking and I simply don't think you can do this with an ordinary query. (I'd be happy to be proven wrong, though :})

If you have MySQL 4.1 you can make a nested query where you first find all the productIDs for the products the person _has_ reviewed and then you make a query where you select all the products not returned from the first query.

If you have an older version of MySQL you can't make a nested query, but you can simulate it from PHP (which I'm assuming you're using?). In that case you again make a query to find the products that _have_ been reviewed, construct a list from them and make a new query from PHP where you use NOT IN on that array.

I can't get my head around making it work for more than one person at a time right now, though

damn,
can you explain how I would go about something like this:

Quote:

If you have MySQL 4.1 you can make a nested query where you first find all the productIDs for the products the person _has_ reviewed and then you make a query where you select all the products not returned from the first query.

I do have 4.1, so this would work

thanks again for your help

This is for one specific person only... I haven't been able to test it as
my own server doesn't have mysql 4.1:

Code:

SELECT product.product_name
FROM product
WHERE product.ID NOT IN
(SELECT product.ID
FROM person_review LEFT JOIN product ON person_review.product_ID = product.ID
WHERE person_review.person_ID = 1) //this is where the specific person comes in

(Actually, from snooping around on mysql.com, I think it should be
possible to reformulate to an ordinary query, but if you have 4.1,
there's no need to worry about that :})

I haven't figured out how you'd do all the persons, but I came to
think of something as I re-read your description up top: Are the
persons only going to review _some_ of the products and not all? If
so, don't you have a problem with your database-structure? I mean,
how can you know that a person is supposed to review a product?
That a review isn't in the persons_review table only shows that he
hasn't done it - not that he's supposed to...

If that is the case you could make a lot of things a lot easier on
yourself by changing your persons_review table to hold all reviews
that are supposed to be made and then add an extra column to
store a value indicating whether or not the review has been made
yet.

But then again, you could be making this query to make a person
chose a new product to review, and then my long comment makes
no sense at all... I'm tired ;}

haha, well, my intent is to list all of the products on a user specific page.
The user will then choose from the list, and make the review, when they go back to that page, the product they just reviewed will be gone, so the page will only display which ones they have left to review.
They are supposed to review each product, but I know that won't happen, this is just a way for them to see which ones they have left.

Heh, then I did make no sense =D
At least there was a query for you in the middle of all the nonsense ;}