CODEWITHSUNDEEP
mysqlleft-joinwhere-clause
Exerion
Exerion

Reputation: 469

Filter data from two left joins

I have some simplified data:

mysql> SELECT * FROM tbl_task;
+----+----------+
| id | data     |
+----+----------+
|  1 | data 1   |
|  2 | data 2   |
|  3 | data 3   |
|  4 | data 4   |
|  5 | data 55  |
|  6 | data 166 |
+----+----------+

mysql> SELECT * FROM tbl_parameter;
+----+---------+----------+
| id | task_id | name     |
+----+---------+----------+
|  1 |       1 | hardware |
|  2 |       1 | hardware |
|  3 |       1 | hardware |
|  4 |       2 | hardware |
|  5 |       2 | hardware |
|  6 |       3 | hardware |
|  7 |       3 | hardware |
|  8 |       3 | hardware |
|  9 |       4 | hardware |
| 10 |       5 | hardware |
| 11 |       5 | hardware |
| 12 |       5 | hardware |
| 13 |       6 | hardware |
| 14 |       6 | hardware |
+----+---------+----------+

mysql> SELECT * FROM tbl_parameter_value;
+----+--------------+---------+
| id | parameter_id | value   |
+----+--------------+---------+
|  1 |            1 | modem   |
|  2 |            2 | printer |
|  3 |            3 | 220     |
|  4 |            4 | 24      |
|  5 |            5 | modem   |
|  6 |            6 | printer |
|  7 |            7 | 220     |
|  8 |            8 | gps     |
|  9 |            9 | 24      |
| 10 |           10 | printer |
| 11 |           11 | modem   |
| 12 |           12 | 220     |
| 13 |           13 | 24      |
| 14 |           14 | modem   |
+----+--------------+---------+

So, task have multiple parameters, each parameter have one value. It may seem to not make sense, but this is just simplified data.

I use two joins to get value data:

mysql> SELECT tbl_task.id, tbl_parameter_value.value
    -> FROM tbl_task
    -> LEFT JOIN tbl_parameter ON tbl_task.id = tbl_parameter.task_id
    -> LEFT JOIN tbl_parameter_value ON tbl_parameter.id = tbl_parameter_value.parameter_id;
+----+---------+
| id | value   |
+----+---------+
|  1 | modem   |
|  1 | printer |
|  1 | 220     |
|  2 | 24      |
|  2 | modem   |
|  3 | printer |
|  3 | 220     |
|  3 | gps     |
|  4 | 24      |
|  5 | printer |
|  5 | modem   |
|  5 | 220     |
|  6 | 24      |
|  6 | modem   |
+----+---------+

As you can see, there are some duplicates in task.id column. What I need is to filter this query to return only tasks with parameter values modem AND printer at the same time.

Following query obviously doesn't make any sense:

SELECT tbl_task.id, tbl_parameter_value.value
FROM tbl_task
LEFT JOIN tbl_parameter ON tbl_task.id = tbl_parameter.task_id
LEFT JOIN tbl_parameter_value ON tbl_parameter.id = tbl_parameter_value.parameter_id
WHERE value LIKE '%modem%' AND value LIKE '%printer%'

I tried also:

SELECT tbl_task.id, tbl_parameter_value.value
FROM tbl_task
LEFT JOIN tbl_parameter ON tbl_task.id = tbl_parameter.task_id
LEFT JOIN tbl_parameter_value ON tbl_parameter.id = tbl_parameter_value.parameter_id AND tbl_parameter_value.value LIKE '%modem%' OR tbl_parameter_value.value LIKE '%printer%'

And it gives a wrong results ether.

How do I filter only distinct tasks with parameter values modem and printer at the same time?
What I need to get based on this data is:

+----+
| id |
+----+
|  1 |
|  5 |
+----+

Upvotes: 0

Views: 40

Answers (1)

Jocelyn
Jocelyn

Reputation: 11413

Since you need to find a task with parameter values modem AND printer, it is necessary to join the tables tbl_parameter and tbl_parameter_value twice in the query (using table aliases):

SELECT t.id
FROM tbl_task t
INNER JOIN tbl_parameter tp1 ON t.id = tp1.task_id
INNER JOIN tbl_parameter tp2 ON t.id = tp2.task_id
INNER JOIN tbl_parameter_value tpv1 ON tp1.id = tpv1.parameter_id
INNER JOIN tbl_parameter_value tpv2 ON tp2.id = tpv2.parameter_id
WHERE tpv1.value LIKE '%modem%' AND tpv2.value LIKE '%printer%'

If you want to test if the parameter values are exactly equal to modem and printer, you may replace the WHERE clause with:

WHERE tpv1.value = 'modem' AND tpv2.value = 'printer'

Note: If you use LEFT JOIN instead of INNER JOIN, the query will always return all id values from tbl_task. This is clearly not the intended result.

Read the documentation about the JOIN syntax to learn more.

Upvotes: 1

Related Questions