0

I have three tables 'users', 'friends', 'friendsrequests'.

'Users' contains an id, a firstname and a lastname, 'friends' and 'friendsrequests' both contain users_id_a and users_id_b.

When I search for new friends, I select id's where firstname is LIKE :whatever or lastname LIKE :whatever. However, I want to exclude those id's which are present in the other two tables.

I know how to solve this via application logic, but I also know I shouldn't do this. I know I shouldn't chain the SELECT statements and that I should use joins.

Dave Hogan
  • 3,201
  • 6
  • 29
  • 54
John Smith
  • 752
  • 9
  • 35

1 Answers1

2

You've answered your own question in that you know you can use joins. There are plenty of examples available here on how to do a join in MySQL.

There are several join types but the one you require in this instance is probably a LEFT OUTER. You could do a then do the filtering on the field on the other two tables by using a IS NULL. So what this is doing is joining on the additional tables regardless if there is any data in those tables. Using a WHERE IS NULL to filter out those that are present.

Rather than using joins you could take a WHERE NOT EXISTS approach. This logic might be more up your street if you're not familiar with SQL joins. An example might be:

SELECT * FROM FRIENDS f
WHERE NOT EXISTS (SELECT 1 FROM friendsrequests fr WHERE f.user_id = fr.user_id)

Some examples can be found here: SELECT * WHERE NOT EXISTS

Another approach in using the IN statement or specifically the WHERE NOT IN (SELECT ...)

Hopefully this will guide you if you're still stuck post your exact sql schema and the requirement on a site like http://sqlfiddle.com/ and you'll more likely get more specific response.

Community
  • 1
  • 1
Dave Hogan
  • 3,201
  • 6
  • 29
  • 54
  • As you can tell I am by no means an expert on databases, but I read somewhere that using SELECT ... WHERE NOT IN (SELECT...) is discouraged, since this would be very slow on large tables. Is this true? – John Smith Oct 05 '13 at 10:55
  • I wouldn't say it would be "very slow" in comparison. It depends on query and table size from experience - In real world systems for most queries you'd be hard-pressed to notice any performance difference. Just be aware of it. Here's a good article comparing the three. http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ Reason I mention the different types is that it can be clearer understanding the queries intention. – Dave Hogan Oct 05 '13 at 17:37