complex select question, i think

KashKurcura

ok, i am trying to make a voting system where it will only select members that i have not voted for yet

TABLE Members
MemberID

TABLE Votes
VoteID
MemberID
VoterID
Vote

does anyone know how i would create a select statement that will only show members that arnt in the Votes table? is that even possible?

mrmigu

well, the version of mysql that I use doesnt like:
select * from members where memberID not in (select memberid from votes)

so when i try to do something like this i use:

select * from members m left join votes v on m.MemberID=v.MemberID where v.voteId=NULL(or '' cant remember which works)

KashKurcura

hi thanks for your reply..

this is the query that i have been working on, and there should be acouple thousand results and its turning up 0 results.. Any idea what I might be doing wrong?

SELECT ScreenName
FROM Members M
LEFT JOIN Votes V on M.MemberID=V.MemberID
WHERE v.VoteID IS NULL
AND VoterID = 116

Roger_Ramjet

The query to find members who have not voted is

$sql = "SELECT * FROM Members m LEFT JOIN Votes v USING(memberid) WHERE v.memberid IS NULL";

KashKurcura

when i attach my VoterID to the query it brings up 0 results, it should return at least acouple thousand results.

the trick is that i only want to show members from the Members table that i myself have note voted for yet. :bemused:

Roger_Ramjet

Well that is an entirely different problem to what you started with.

With sub-query

 $sql = "SELECT * FROM Members m LEFT JOIN (SELECT * FROM Votes WHERE VoterID=116) AS v USING(memberid) WHERE v.memberid IS NULL";

Without sub-query

 $sql = "SELECT DISTINCT m.* FROM Members m, Votes v WHERE v.VoterID <> 116";

KashKurcura

thanks for the reply... i tried running the one with out a sub query and it had returned members that i had alread voted for.. ?

Roger_Ramjet

OK so now I begin to understand your data now. Of course there are other voters so all members will most likely be returned :queasy:

You will have to go with the sub-query form - and there are a couple of ways to do it with a sub-query but I think the one I chose would be most efficient.

If your version does not support sub-queries then it will have to be done with 2 queries. It is easy to get all the members you have voted for, but not the ones you haven't voted for, not in 1 query.

2 queries

$sql1 = "SELECT MemberID FROM Votes WHERE VoterID=116";
$result1 = mysql_query($sql1);
$voted='';
while ($row=mysql_fetch_array($result1)) {
   voted .= $row['MemberID'] . ',';
}
$voted = rtrim($voted, ',');
$sql2 = "SELECT MemberID FROM Members WHERE MemberID NOT IN ('" . $voted . "')";

KashKurcura

hmm i see now, i think i just may update my mysql 4.1 so i can run sub queries.. to me that dont really look as efficient ;-) so now i guess i gotta look to see how to update mysql :-)) thanks for your help!

KashKurcura

hey looks like this one does the trick i think..

$sql = "SELECT FROM Members m LEFT JOIN (SELECT FROM Votes WHERE VoterID=116) AS v USING(memberid) WHERE v.memberid IS NULL";

i am NEW to this sub-query thing, is that written the best way possible?

Roger_Ramjet

Yes, that should be as good as you would get. The subquery will return a table called v that contains all the memberids you have voted for, so then it is a straigtforward left join to that table with the IS NULL.

Using sub-queries to return tables as if they were physical database tables is really usefull and something you will come to do more and more as you get the hang of it.