SQL Problem Thread

Of course I know this isn't a php or mysql forum, but i've got a little mysql problem, and this is the only forum I go to. I know there's a lot of php guys here, so you might have run across something like this.

anyways...

I've got my query

SELECT * FROM hates WHERE hateid NOT IN (SELECT hate FROM votes WHERE member='$memberid') $addtosql ORDER BY rand() DESC LIMIT 1

works fine, blah blah. On mysql 4.1+ of course it turns out the hosting I have is only 3.12, so ive spent the last day trying to beat this into something that would work without a subquery. Problem is I can't. Spent 2 days on it orignally before I realized I had to use one. Would anyone else happen to have any ideas?

ok... sub-queries are anyways a bit shitty stuff unless you are forced to 'one' and only one query. now what goes for this case i'm a bit stuck on what exactly you try to do. so far i'm with this:

tables 'hates' and 'votes'. 'hates' contains 'hateid' and most probably more. 'votes' contains 'hate' and 'member' and most probably 'hateid', but that's exactly the strange part. you look for 'hateid' inside a query which only returns 'hate' columns. hence with other words you try to look for 'hateid' column but there is none. most probably shitty mysql 4.1 is lazy on you there as from a logical point of view this query is bullshit. maybe giving some more infos on what you try to do would help to nail down the problem but this mismatch there is already a problem.

EDIT: another point. you have 'LIMIT 1' hence you look for only 'one record'. limit is anyways a bit shitty and usually is used only if the query is going the wrong way. if i get this right you look for a 'member' who is not marked with true inside 'hate' or whatever value this column takes. this could be solved by using a query along this line:

SELECT COUNT(votes.member) AS count
   FROM hates INNER JOIN votes ON hates.hateid=votes.hateid
   WHERE votes.member='$memberid' AND votes.hate<>'true' $addtosql

this would yield a result-set ( "count", "1" ) with 1 beeing the number of matching records. you could then simply test if the number is 0 ( no match ) or >0 ( match ).

Edited by (in order): Dragonlord, Dragonlord, Dragonlord

Not really what its doing exactly. What I've got is essentially a list of things people can vote on, and a list of votes. I want my resultset to be a list of all the items that a certain user has not voted for.

hate is actually the same as hateid, Im a moron at naming stuff.

I'm trying joins now, and I can use

SELECT hates.*  FROM hates LEFT JOIN  votes ON hates.hateid=hate WHERE hate IS NULL

but I can't figure out how narrow that down to all items that are unvoted on by a specific member, as opposed to all members.

Edited by: duckedtapedemon

Also, I would say (I've had errors from this before), that you have strings inside quote marks, which generally screws things up. Try using

"SELECT * FROM ".$string." WHERE ".$string."=".$string."

The ".$string." works well to make you able to have variables in SQL queries.

let's see if i get this right. let's say we have a table 'options' which contains all the options somebody can vote and a table 'votes' which contains a record for each vote members have made. now we want a subset of the 'options' table which contains nothing found in 'votes' matching our $memberid. now we can think a bit 'around the corner':

SELECT options.name, COUNT(1) as option_count
FROM options INNER JOIN votes ON options.option_id=votes.option_id
WHERE votes.member_id='$memberid'
GROUP BY options.name
HAVING COUNT(1)=0

now what does this do... it's a bit thought around the corner. in fact what we want is to 'count' how many times an option has been voted by the user. in fact this can only be 0 or 1 but that's enough. we join the two tables. then we group by 'name'. this is required as this way we get a table with all names and the count next to it. the COUNT(1) is simply a performance hack and is the same as COUNT(*) just faster. what you get is then a table with the name of the vote option and next to it either 0 or 1. the rest should be easy.

EDIT: i added the HAVING clause. what it does is simply keeping only counts which are 0, hence effectively keeping only the options 'not' voted for.

Edited by (in order): Dragonlord, Dragonlord, Dragonlord

well that seems like it should work... but it doesn't.

Thats doing what I described, but when I port it to my tables, it returns nothing.

depends on your tables. if you could post the layout of the two tables in question ( only the fields required ) would help as then i could slap together a query that works for your system.

votes is member and hate

hates is hateid. (I'd also like hates (the actually text that appears) and hater (the id of the user that added the "hate") yay for nested quotes)

hates would be the table that contains the options.

not sure if i fully get this but i tried on my postgresql server here that situation. the query above doesn't work because NULL values are not allowed usually in a database or dropped in queries. now there's a second way to 'think around the corner' for this problem which uses not nested/sub queries but combined queries, which should be possible in your release. combined queries are simply conducted each after the other and the result combined. let's see if this works for you.

SELECT name FROM hates
EXCEPT
SELECT hates.name FROM hates INNER JOIN votes ON hates.hate_id=votes.hate_id WHERE votes.member_id=8 GROUP BY hates.name

i added a field 'name' to hates for testing purpose. that's most probably different for your case. now what it does is the following. first we simply query all possible options from 'hates'. then we remove from them all entries that the next query yields, which are all entries the member in question voted on. hence matching records are simply eliminated resulting in only those entries to stay which he has not voted on.

this one is close to what you had in the first place but it doesn't use a sub query. something better i can't think up at the moment. otherwise you would have to look once on EFNet for an SQL channel. i've been in a postgresql one some time ago and those guys know what they talk about

I don't use MySQL normally (I use M$ SQL 2K at work a lot, though), but I seem to recall that MySQL prior to version 4.0 does not support sub-selects at all.

Under MySQL 3.x I believe the only real option you have is to build the WHERE condition in a separate operation (probably in PHP since I'm guessing that's what you're using to call the SQL query in the first place), then attach that to the main query.

Thanks for all the help guys. Sorry I didn't thank you sooner.