Join table on multiple ids

Question

exp table: This is where I store entries while keeping the domains and sub domains in a separate table. The sub_domain column can have one or multiple ids that match the id column in misc.

+----+---------+-----------+------------+-----------+--------+-------------+------------+------------+
| id | user_id | job_type  | experience | exp_title | domain | sub_domain  | start      | end        |
+----+---------+-----------+------------+-----------+--------+-------------+------------+------------+
| 83 |     268 | Full Time | dasdfg     | dest      | 76     | 89,91,93,95 | 07/15/2012 | 07/31/2012 |
| 84 |     269 | Full Time | abcdef     | title     | 77     | 89          | 07/15/2011 | 07/31/2011 |
+----+---------+-----------+------------+-----------+--------+-------------+------------+------------+

misc table:

+-----+----------------------------------------+--------------+
| id  | name                                   | category     |
+-----+----------------------------------------+--------------+
|  89 | Name1                                   | category    |
|  91 | Name2                                   | category    |
|  93 | Name3                                   | category    |
|  95 | Name4                                   | category    |
|  55 | Name5                                   | category    |

I was wondering how to change LEFT JOIN misc c ON a.sub_domain=c.id if there are more than one sub_domains in the exp table while keeping in mind that there can be one id as well.

$query_str = "SELECT a.*, b.name, c.name AS sub_name
            FROM exp a 
            LEFT JOIN misc b ON a.domain=b.id
            LEFT JOIN misc c ON a.sub_domain=c.id
            WHERE a.user_id = ?";

Answer 1

Try this

$query_str = 
    "SELECT a.*, b.name, c.name AS sub_name 
    FROM exp a  
    LEFT JOIN misc b ON a.domain=b.id 
    LEFT JOIN misc c ON locate(concat('','',c.id ,'',''),'','',a.sub_domain,'','') >0
    WHERE a.user_id = ?"; 

Answer 2

You'd have to do this in two queries. One of the main reasons is that if ever you manage to accomplish what you want to do, you'd be returning rows with practically the same data except for the sub_name column. That's really unnecessary.

You can first JOIN the two tables on a.domain = b.id and then construct a second query that uses an IN(...) statement for all the subdomain values. It'd be something like this:

$subdomains = explode(',', $data_from_first_query['sub_domain']);
if (count($subdomains)) {
  $q2 = '
    SELECT
      b.name
    FROM 
      misc AS b 
    WHERE b.id IN ('.implode(',',array_fill(0, count($subdomains), '?')).')';
}

Then use foreach to bind values in your placeholder.

But then again, it is good practice to make sure your database is normalized.

Answer 3

It's not good to have columns with comma separated values. Read the answer on the question:

Is storing a comma separated list in a database column really that bad?

Short answer is: It's really that bad.

---

Until you fix the design, you can use the FIND_IN_SET() function:

$query_str = "
        SELECT a.*, b.name, c.name AS sub_name
        FROM exp a 
          LEFT JOIN misc b ON a.domain = b.id
          LEFT JOIN misc c ON FIND_IN_SET(c.id, a.sub_domain) 
         WHERE a.user_id = ?
             ";