[SOLVED] Select query, with 2 tables

[Philip]

Okay, I admit I'm still pretty noob at MySQL... But here's what I got:

Table 'users':

id

username

link

clicks

hits

suspend

vacation

maxed

 

1

joe

abcd

1

0

0

0

0

2

bob

dbca

0

0

0

0

0

3

jim

linksomewhere

0

0

0

0

0

 

Table 'clicks':

to

from

dbca

1

 

Where, clicks.to is linked to users.link and clicks.from is linked to users.id.

 

What I am trying to do is something along the lines of Select stuff from tables users where user isn't suspended and user isn't on vacation and user has not clicked that link.

 

Example: Before joe clicks bob's link, I should be able to print a list and look like:

joe [abcd], bob [dbca], jim [linksomewhere]

Now, joe clicks bob's link and it inserts bob's link and joe's id into the table 'clicks' (as seen as the example entry in the tables above). Now, when I print the list for user joe, it shouldn't show bob's link anymore.

joe [abcd], jim [linksomewhere]

 

What I have kinda works, but it'll display some results twice. I've also aimlessly tried a left join. Here's what I've got (left joined):

SELECT users.username, users.link, users.clicks, users.hits, users.maxed, (users.clicks - users.hits) AS ch
FROM  `users` 
LEFT JOIN  `clicks` ON users.id != clicks.from
AND users.link != clicks.to
WHERE users.suspend =0
AND users.vacation =0
ORDER BY ch DESC

(normal join):

SELECT users.username, users.link, users.clicks, users.hits, users.maxed, (users.clicks - users.hits) AS ch
FROM  `users` ,  `clicks` 
WHERE users.suspend =0
AND users.vacation =0
AND users.id != clicks.from
AND users.link != clicks.to
ORDER BY ch DESC 

[Philip]

Finally got it to work, had to use a sub query. If anybody has any improvements to the following code, I would really appreciate the feedback

SELECT users.username, users.link, users.clicks, users.hits, users.maxed, (users.clicks - users.hits) AS ch
FROM `users`
WHERE 
NOT EXISTS (SELECT `from` FROM `clicks` WHERE clicks.to=users.link AND clicks.from=[CURRENT USER, get this value from PHP])
AND users.suspend =0
AND users.vacation =0
ORDER BY ch DESC

[fenway]

I'm sure this can be written with a LEFT JOIN is NULL... just TLDR for now.