MySQL Query to accomplish INTERSECT

[ochaos]

Hello,

I am having a hard time getting anything to work with Inner Join for the results I am trying to achieve.

I have one table (data) that has the following
id user_id data
1 100 Test1
2 101 Test2
3 100 Test 3
4 121 Hi
5 101 Hello
6 357 Yo

and another that lists "friends"
id user_id friend_id
1 100 101
2 100 102
3 100 300
4 100 357


Some people have over 1000 friends and the data table can have 1000+ records in it that need to be checked against at any time.

I'd like to compare both tables (see if the current user ID from the second table has any friends records in the data table) and return all the results. So in this example, I would like to return the following as efficiently as possible. (I know I could use something like php array_intersect(), but i'm guessing doing this in MySQL is much more efficient.

Desired Results
2 101 Test2
5 101 Hello
6 357 Yo

I dont know how to get there, any help will be greatly appreciated!

 

[r937]

what is the "current" user?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ochaos]

Sorry, "current" user in this example is user_id 100

Thanks!

 

[ochaos]

Ok, I got this.

Ended up using a JOIN and it did exactly what I was looking for much more efficiently then a PHP function.

 

[r937]

that's what i was going to suggest ;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[ochaos]

Good to hear it was the right choice... I've been doing php/mysql coding for a few years now and never really had a need (or so I thought) for to many JOINs in my statements and I was able to get all the data I needed with specific SQL queries that included multiple tables in the main query.

This project opened my eyes to the some things I will be able to do better in the future.