ASP + MySQL... Need help for tricky select...

[ossiandk]

Hi...



I'm making a "Hot or Not" system in asp and MySQL, and I'm having problems selecting posts that people have not voted for earlier.

I hope this info is enough...

I got two tables:

PICTURES - Fields: pid (picture id) | gender (gender of the person on the picture)

VOTES - Fiels: vid (vote id) | pic (picture id) | uid (user id of the person voting, random generated and called from a cookie.. i.e. vbgpnuxhebtdkkbnncdfeorsyjevhj) | rating (rating 1-10)

Now.. what I want to dois to only select post that have not been voted for earlier.. And I tried to do it like this:

Conn.Execute("SELECT * FROM pictures, votes WHERE ((pictures.pid != votes.pid) || (votes.uid != '" & strUserId &"')) && (pictures.gender = '"& strGender &"')")

But it doesn't work.. I always get the same id out..

I Hope this info is enough.. Otherwise ask and I'll provide all u need..

Thank you so much!! I Worked like 15 hours on this stupid line.. lol

All the best from Copenhagen..

 

[lovejaeeun]

I think this would work. But I'm not too sure about its efficiency.

SELECT pid
FROM PICTURES
WHERE pid NOT IN(
SELECT DISTINCT pic
FROM VOTES
);

 

[ossiandk]

Damn..I get:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[MySQL][ODBC 3.51 Driver][mysqld-4.0.18-nt]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT DISTINCT pid FROM VOTES)' at line 1
/makelove/inc/vote.asp, line 39

Line 39: RS = Conn.Execute("SELECT pid FROM pictures WHERE pid NOT IN(SELECT DISTINCT pic FROM VOTES)")

 

[amorous]

SELECT pid FROM pictures INNER JOIN
Votes on pictures.pid = votes.pid
WHERE rating IS NULL

Assuming that if rating is null implies that picture has been not voted earlier.

-VJ

 

[amorous]

it would also be better if you provide some sample data of both of your tables and the kind of result you want.

thanks

VJ

 

[Tarwn]

Other than the Inner Join, I think Amorous has the bet thing going. I think you may want a Left Join, Left Outer Join, etc. Darnit why don't db's support the same keywords, argh. Basically you want all the ids in Left table matche to right table, even if there is no record in right table, then you just look for only the ones with Nulls in right and you have everything that wasnt voted on.

Sorry, long explanation (and probably confusing), back to you guys,

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

 

[amorous]

I think Amorous has the bet thing going

Dint quite understand what you meant by that. I am still in the learning stages and i feel sharing suggestions and knowing mistakes is the best way of learning.

 

[Tarwn]

Sorry, there was a typo in there. I meant to say "Amorous has the [highlight]best[/highlight]".

Basically I was just explainnig that I thought you were on the best track so far and that the only thing I would modify would be to make it a Left or Left outer Join. Other than that I was just explaining a little why the select would work by doing that.

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
The never-completed website:

http://www.tiernok.com/

 

[amorous]

Thanks Tarwn,

You guys are awesome. I have learned a lot from this forum.

-VJ