CODEWITHSUNDEEP
phpmysqlsql
Александр
Александр

Reputation: 65

Retrieve value from mysql if it does not exist in other table

I try to figure out something but it doesn't really go well. I have 1 database with two tables.

Table 1: Users Table 2: Voted

Table 1 has: userid Table 2 has: votemyid and votevotedid

The second one is the same as userid, how can i make sure that it doesn't select values from users if that value exists as votevotedid in the table voted?

my SQL code till now is:

$result = mysql_query("SELECT *, ( 6371 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin(radians(lat)) ) ) AS distance count(*) FROM users,voted WHERE voted.votevotedid not in (select users.userid from users) HAVING distance < $distance ORDER BY RAND() LIMIT 1");

I can do this, then do a second mysql_query for the voted database, but that get's to much

Upvotes: 1

Views: 179

Answers (4)

Александр
Александр

Reputation: 65

This seems to work

$result = mysql_query("SELECT *, ( 6371 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin(radians(lat)) ) ) AS distance FROM users AS u LEFT JOIN voted AS v ON u.userid = v.votevotedid WHERE v.votemyid != '1' HAVING distance < $distance ORDER BY RAND() LIMIT 1");

When i change the 'votemyid' to number 2 (in other words userid 2 voted on userid 1) it shows up for me (as userid 1)

Nevermind, doesn't work.

If there is nobody in the voted table it also doesn't return anything

When i Add:

WHERE v.votemyid IS NULL

And there is anyone else in the table, let's say: userid 1 voted on userid1, i (id 1) gets o results back

Upvotes: 0

Fathah Rehman P
Fathah Rehman P

Reputation: 8741

select * from users where userid not in (select votevotedid from votevotedid )

Upvotes: 0

smozgur
smozgur

Reputation: 1812

The method is using the LEFT JOIN:

SELECT u.userid 
FROM Users AS u
LEFT JOIN Voted AS v ON u.userid = v.votevotedid
WHERE v.votemyid IS NULL

What LEFT JOIN does returns all users records and match with voted table and exclude records where voted table return a matching record (by using v.votemyid IS NULL)

I am not sure about your SQL without seeing the exact table structures but it should be something like following with LEFT JOIN.

$result = mysql_query("SELECT u.*, ( 6371 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin(radians(lat)) ) ) AS distance 
FROM users AS u 
LEFT JOIN voted AS v ON users.userid = v.votevotedid
WHERE v.votemyid IS NULL
HAVING u.distance < $distance 
ORDER BY RAND()
LIMIT 1");

Upvotes: 1

Merlin Denker
Merlin Denker

Reputation: 1418

Try adding the following:

WHERE userid NOT IN (SELECT DISTINCT(votevoteid) FROM voted)
AND votevoteid=userid

Upvotes: 0

Related Questions