How can I join these 3 tables?

Question

MySQL "table_a":

+----+-------+
| id | title |
+----+-------+

Now I can do a search like so:

$term = mysql_real_escape_string($_GET['term']);

mysql_query("SELECT * FROM `table_a`
WHERE MATCH(`title`) AGAINST('$term' IN BOOLEAN MODE) LIMIT 0,5");

However I want to add another compontent.

MySQL "table_b":

+----+----------+
| id | category |
+----+----------+

MySQL "table_c":

+------------+------------+
| table_a_id | table_b_id |
+------------+------------+

So, I want to look for a specific category in table_b that is linked to table_a according to table_c.

BUT, it isn't always the case that a category is linked to table_a (so it could happen that there just aren't any entries in table_c that are connected to table_a).

AND, if there is a category linked to table_a I want to be able to either search for the title in table_a or the category in table_b (so both should be possible, so category shouldn't overrule title, or the other way around). But if that's not possible, then title should overrule category.

Here's what I came up with so far, but the problem is

a) it doesn't work

b) it doesn't include the title OR category as I had just explained

$term = mysql_real_escape_string($_GET['term']);

mysql_query("SELECT a.*, LEFT JOIN (SELECT c.table_a_id FROM table_b AS b
, table_c AS c WHERE b.category = '$term' AND b.id = c.table_b_id)
AS d ON d.table_a_id = a.id FROM table_a AS a
WHERE MATCH(a.title) AGAINST('$term' IN BOOLEAN MODE) LIMIT 0,5");

Any help would be greatly appreciated.

Just a sidenote from reading your PHP code: you need to escape `$_GET[term]`, don't use it like this. — Till, Sep 17 '11 at 21:21
@Till I am I just didn't include it in the code above. But thanks ;) I'll update my question. — , Sep 17 '11 at 21:22
Is it intentional that multiple categories could apply to a title? — Merlyn Morgan-Graham, Sep 17 '11 at 21:22
@Merlyn Yup, there can be an infinite number of categories to a title. — , Sep 17 '11 at 21:24

score 0 · Accepted Answer · edited May 23 '17 at 10:34

You really want to join the table_b and table_c data together and the perform your left join against that. Something like this should work for you. If there are multiple categories entries that match, you will have duplicate records, since you only want data from a.*. For debugging, I would add d.* as well so you can see why there are duplicates if there are any:

SELECT a.*
from   table_a a
       LEFT JOIN (SELECT c.table_a_id, b.category
                  from   table_b b,
                         table_c c
                  where  c.table_b_id = b.id) d
                  on     a.id = d.table_a_id
WHERE d.category = '$_GET[term]'
or    MATCH(a.title) AGAINST('$_GET[term]' IN BOOLEAN MODE) 
LIMIT 0,5

But, if you are doing anything other than testing, you absolutely have to escape the $_GET terms or, even better used parameterized SQL. If you aren't familiar with creating prepared statements, see this StackOverflow answer.

That worked beautifully. Thanks so much, dlawrence! But yeah, it's just for testing, otherwise I would have included `mysql_real_escape_string`, but thanks for pointing it out anyway. — , Sep 17 '11 at 21:36

How can I join these 3 tables?

1 Answers1