Select from table where value in common with another table

Question

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.

Answer 1

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...

Answer 2

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).