CODEWITHSUNDEEP
mysql
Telmo
Telmo

Reputation: 61

Select from table where value in common with another table

I have two tables ( visited and purchased )

visited -> id,product,user_ip
purchased -> id,product,user_ip

which eventually have the the same value in the column user_ip. Example:

visited ->
1,product1,192.168.1.1
2,product2,192.168.1.1
3,product3,192.168.12.12
4,product4,192.168.12.12

purchased ->
1,product3,192.168.12.12

Is possible to select all products from visited which have user_ip in common with purchased? In the above example I would need to select id3 and id4.

Upvotes: 6

Views: 15781

Answers (2)

Vadiklk
Vadiklk

Reputation: 3764

Try this, post a comment if you need me to edit. 

SELECT *
FROM visited
INNER JOIN purchased
ON visited.user_ip=purchased.user_ip
ORDER BY visited.id

You could change the select to only what you need btw (if you only need id).

Upvotes: 2

Lukas Eder
Lukas Eder

Reputation: 220902

Try this:

SELECT *
FROM visited
WHERE user_ip IN (SELECT user_ip FROM purchased)

This may be slow if you don't have the correct indexes set up on the user_ip columns...

Upvotes: 10

Related Questions