query-trouble

qrt123

Hi there.

I have 3 tables, layed out like this example:

products:

id
product
description

Users:

id
user
pass

poll:

id
productid
userid
option1
option2

Now I would like to list all the products a given user has NOT voted on.
And god knows I've tried to make a query that would give me that.
I can get those a given user HAS voted on. No problem. But the opposit. That doesn't seem to work.
There is no need to query the user table, as the userid is passed on by session, via the loginscript.

Any suggestions?
I don't know if I just misunderstand something in the field of querying, or the table-layout???

Kinda newbie in database and all that normalisation stuff.

Thanks in advance
qrt123

devinemke

let's say we are talking about user #123:

SELECT poll.productid, products.product FROM  poll, products WHERE poll.productid = products.id AND poll.userid  != 123

tekky

Correct me if I'm wrong, but wouldn't this query produce ill results if for example..

poll:
id
productid
userid
option1
option2

had this data in it...

id=1
productid=23
userid=4
option1=2
option2=1

id=2
productid=23
userid=123
option1=2
option2=1

id=3
productid=36
userid=56
option1=0
option2=2

wouldnt that return
productid 23 and 36 even though user 123 voted on product 23?

devinemke

good call. if your RDMS supports subqueries you could do:

SELECT poll.productid, products.product FROM poll, products WHERE poll.productid = products.id AND poll.userid != 123 AND poll.id NOT IN(SELECT id FROM poll WHERE userid = 123)

tekky

you could do the same thing as a sub query basically using a temp table and SELECT INTO

just do SELECT * INTO tempTable FROM pollinfo WHERE pollinfo.userid = '123'

then you can just add to the end of your previous query AND != temptable.pollid

qrt123

Hi there.

Thanks for the replies tekky & devinemke.
I don't think I made myself clear from the start.

If there are, let's say, 100 entries in the product-table. And 50 of these has been voted on, and user '123' has voted on 30 of these.
Then I would like to be able to show user '123' a list of the 70 products, that he has not voted on.

20 products from the poll-table, that other users has voted on, and the remaing 50 from the products-table, that no one has voted on yet.

Don't know if that is possible.
Came to think of an alternative solution.
Putting some dummy-data in the poll-table when a new product is uploaded. Overwriting it the first time somebody vote on the product.
The poll-table will always have the same number of products as the products-table.

Haven't tried yet though.
Just a possible solution.

thanks
qrt123

tekky

you could try it like this...

SELECT productid INTO tempTable FROM polls WHERE userid = '123'

SELECT prod., temp. FROM products AS prod, tempTable AS temp WHERE prod.id != temp.productid

I would think something like that would work