MySQL - Select from one table depending on other table

Question

I have 10 users in a DB. Each user can post as many links as he wants. Each user can see all the links posted by the other users.

If one user clicks on a link, ex: google.com, he'll not see it again, but those users who haven't clicked that link, they can still click that link.

For this I have 2 tables (perhaps they are badly built, besides they are not related with foreign keys).

linksPosted

id   |   link   |   user
1        g.com      john
2        h.com      patrick
...

clicksMade

id   |   link   |   user
1        g.com      jack
2        h.com      nick
...

So, ALL the links posted can be seen by all the users among them. Everytime a link is posted, this will be added to linksPosted table. OK. Then, for example, if Jack click g.com, Jack will be able to see other links from other users, but g.com will not appear again for him, because he has clicked it, but Nick will be able to click g.com because he hasn't clicked it.

How can I do this in a SQL query?

Thanks.

Fathah Rehman P · Accepted Answer · 2013-01-03 05:34:40Z

1

To get links that are visible to jack, use following query

  select * from linksposted linktbl where linktbl.link not in(select 
    clickstbl.link from clicksmade clickstbl where clickstbl.user='jack')

or you can use

   select linktbl.id,linktbl.link,linktbl.`user` from linksposted linktbl 
   left join clicksmade clickstbl on linktbl.link=clickstbl.link and
   clickstbl.user='jack' where clickstbl.link is null

edited Jan 3, 2013 at 5:34

answered Jan 3, 2013 at 5:02

Fathah Rehman P

8,8014 gold badges43 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

frankie3 Over a year ago

it doesn't work as I want, because if I click a link as Jack, then I log in as Nick, Nick can't click the the link that Jack has clicked because this link doesn't appear anymore, and Nick must be able to click it because he hasn't click it yet

Fathah Rehman P Over a year ago

@frankie3 - "it doesn't work as I want, because if I click a link as Jack, then I log in as Nick" ???? if you click a link as jack then its should log as jack clicked link, not as nick

frankie3 Over a year ago

that's what I say, the click is registered as jack, ok nice, but then i log out, i loin as nick and the link that jack has clicked doesn't appeare anymore, when in fact nick hasn't clicked the link that jack has clicked, summing up, the link dissapear from the main site if is clicked by one user, but another user must be able to click the same link

Fathah Rehman P Over a year ago

@frankie3 - my above query will show links that are visible to jack. If you want to get links that are visible nick then you have to change jack in query to nick . got it?

user1408818 · Accepted Answer · 2013-01-03 04:00:29Z

0

i think you can solve this by adding a "NOT IN" to the select... "eg select * from linksPosted where id NOT IN (SELECT id FROM clicks made where user = $myuser)" be aware that this is a really quick and dirty solution... the nicer one would be an outer join. i think it's something like select * from linksposted l right outer join clicksmade c on c.id = l.id but you have to do some research on that... but it'll be the right direction

answered Jan 3, 2013 at 4:00

user1408818

135 bronze badges

Collectives™ on Stack Overflow

MySQL - Select from one table depending on other table

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related