Hi I'm mysql table like this
mysql> mysql> SELECT ID,type FROM sys_tags; +----+------+ | ID | type | +----+------+ | 1 | 0 | | 2 | 0 | | 3 | 0 | | 4 | 0 | | 5 | 0 | | 6 | 0 | | 7 | 0 | | 8 | 0 | | 9 | 0 | | 10 | 1 | | 11 | 1 | | 12 | 1 | | 13 | 1 | | 14 | 1 | | 15 | 1 | | 16 | 1 | | 17 | 1 | +----+------+ 15 rows in set (0,00 sec)
mysql> SELECT * FROM `sys_tags_groups`;
+----+----------+--------+--------+------+
| ID | ID_group | ID_row | id_tag | type |
+----+----------+--------+--------+------+
| 0 | 3123 | 1 | 10 | 1 |
| 0 | 3123 | 2 | 11 | 1 |
| 0 | 3123 | 3 | 6 | 0 |
| 0 | 3123 | 4 | 2 | 0 |
| 1 | 3123 | 5 | 13 | 1 |
| 1 | 3123 | 6 | 3 | 0 |
| 0 | 5142 | 7 | 12 | 1 |
| 0 | 5142 | 8 | 15 | 1 |
| 0 | 5142 | 9 | 6 | 0 |
| 0 | 5142 | 10 | 2 | 0 |
+----+----------+--------+--------+------+
10 rows in set (0,00 sec)
okey I'm try explaining what I mean example
+----+----------+--------+--------+------+ | ID | ID_group | ID_row | id_tag | type | +----+----------+--------+--------+------+ | 0 | 3123 | 1 | 10 | 1 | | 0 | 3123 | 2 | 11 | 1 | | 0 | 3123 | 3 | 6 | 0 | | 0 | 3123 | 4 | 2 | 0 |
its one group see row type for this group I'm have 2 row with type 1 and 2 row with type 0, next I'm wanna get this group if I'm have tags with type 1 and type 0 example my enter values
example record have 1 tag from group type = 1 and one tag from group with type = 0 its true
+----+----------+--------+--------+------+ | ID | ID_group | ID_row | id_tag | type | +----+----------+--------+--------+------+ | 0 | 3123 | 1 | 10 | 1 | <-- success have tag with type = 1 | 0 | 3123 | 2 | 11 | 1 | | 0 | 3123 | 3 | 6 | 0 | <-- success have tag with type = 0 | 0 | 3123 | 4 | 2 | 0 |
next
example my enter values
how you see my record have one tag from group 0 but not one tags from group 1 its false
+----+----------+--------+--------+------+ | ID | ID_group | ID_row | id_tag | type | +----+----------+--------+--------+------+ | 0 | 3123 | 1 | 10 | 1 | <---- no one tag with type 1 | 0 | 3123 | 2 | 11 | 1 | <---- no one tag with type 1 | 0 | 3123 | 3 | 6 | 0 | | 0 | 3123 | 4 | 2 | 0 |
next example
example my enter values - id_tag = 6 (type = 0) - id_tag = 2 (type = 0) - id_tag = 15 (type = 1) - id_tag = 11 (type = 1)
okey here I'm have one or more tags from tags with type = 0 and one or more from type = 1 its true
+----+----------+--------+--------+------+ | ID | ID_group | ID_row | id_tag | type | +----+----------+--------+--------+------+ | 0 | 3123 | 1 | 10 | 1 | | 0 | 3123 | 2 | 11 | 1 | | 0 | 3123 | 3 | 6 | 0 | | 0 | 3123 | 4 | 2 | 0 |
example my enter values - id_tag = 7 (type = 0) - id_tag = 3 (type = 0) - id_tag = 15 (type = 1) - id_tag = 11 (type = 1)
its false
+----+----------+--------+--------+------+ | ID | ID_group | ID_row | id_tag | type | +----+----------+--------+--------+------+ | 0 | 3123 | 1 | 10 | 1 | | 0 | 3123 | 2 | 11 | 1 | | 0 | 3123 | 3 | 6 | 0 | <--- not have tags with type 0 | 0 | 3123 | 4 | 2 | 0 | <--- not have tags with type 0
How do I correctly create a request to mysql ?
UPD I think I found way
$enterVals = [7,3,15,11];
for ($i = 0; $i < count($enterVals);$i++) {
$tagsWHERE[] = "id_tag=".intval($enterVals[$i]);
}
$sql->("SELECT ID_group,ID
FROM sys_tags_groups
WHERE ".implode(" OR ",$tagsWHERE)."
GROUP BY ID_group,ID
HAVING SUM(type = 1) AND SUM(type = 0)")
$result = [];
$enterVals = [6,2,15,11];
$result = [{"ID_group":3123,"ID":0},{"ID_group":5142,"ID":0}];
and I'm have next question - how I can get better way ? ) I'm asking because HAVING in request its not better way as I know )