CODEWITHSUNDEEP
phpmysqlselectdistinct
Ridgeback
Ridgeback

Reputation: 31

mySQL: get values from multiple lines with all wheres

I have the following table:

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `left_id` int(11) NOT NULL DEFAULT '0',
  `right_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
INSERT INTO `test` (`id`, `left_id`, `right_id`) VALUES (2, 111, 90),
   (3, 111, 91),(4, 111, 92),(5, 111, 93),(6, 111, 94),(7, 111, 95),
   (8, 222, 90),(9, 222, 91),(10, 222, 92),(11, 222, 93),(12, 222, 94),
   (13, 222, 95),(14, 333, 90),(15, 333, 91),(16, 333, 92),(17, 333, 93),
   (18, 333,94),(19, 333, 100);

which looks like

id | left_id | right_id 
2  | 111     | 90
3  | 111     | 91
4  | 111     | 92
5  | 111     | 93
6  | 111     | 94
7  | 111     | 95

8  | 222     | 90
9  | 222     | 91
10 | 222     | 92
11 | 222     | 93
12 | 222     | 94
13 | 222     | 95

14 | 333     | 90
15 | 333     | 91
16 | 333     | 92
17 | 333     | 93
18 | 333     | 94
19 | 333     | 100

left_id is a record of a "news" and right_id records the categories that the news record belongs to.

Now I want all left_ids (distinct) that have EXACTLY/ALL right_ids 90 & (91 OR 92) & 93 & 94 & 95

In long sentence: Give me (distinct) the left_id, where right_id is

(90 & 91 & 93 & 94 & 95)
OR
(90 & 92 & 93 & 94 & 95)

The result must be 111 and 222; not 333, because 333 has NOT 95.

Upvotes: 2

Views: 79

Answers (6)

Pinke Helga
Pinke Helga

Reputation: 6702

You could use a bit mask. You find the position p in a set, shift the value 1 p times left and once right. 1st position results in binary 0001, 2nd in 0010, 3rd in 0100 aso. Aggregate this by binary OR.

Now you can easily build conditions. mask = mask & aggregate means every bit set in mask is also set in aggregate. mask & aggregate means at least one bit of the mask is set in aggregate.

SELECT
  `left_id`,                        # 1  2  4  8  16 32
   BIT_OR(1<<FIND_IN_SET(`right_id`, '90,91,92,93,94,95')>>1) `right_or`
FROM
  `ids`
GROUP BY
  `left_id`
HAVING   1|8|16|32 = (1|8|16|32) & `right_or`
   AND   (2|4) & `right_or`
;

same with condition 91 or 95:

SELECT
  `left_id`,
  BIT_OR(1<<FIND_IN_SET(`right_id`, '90,91,92,93,94,95')>>1) `right_or`
FROM
  `ids`
GROUP BY
  `left_id`
HAVING    1|4|8|16 = (1|4|8|16) & `right_or`
   AND    (2|32) & `right_or`
;

Of course you can precalculate constants, e.g. 1|4|8|16 = 29. I wanted to show a readable example.

Upvotes: 0

mitkosoft
mitkosoft

Reputation: 5316

You can use GROUP_CONCAT() together with REGEXP for that in order to match exact values:

SELECT
    left_id,
    GROUP_CONCAT(DISTINCT right_id ORDER BY right_id) gc
FROM
    test
GROUP BY
    left_id
HAVING
    gc REGEXP '90.+(91|92).+(93).+(94).+(95)'

Output is:

+---------+-------------------+
| left_id | gc                |
+---------+-------------------+
|     111 | 90,91,92,93,94,95 |
|     222 | 90,91,92,93,94,95 |
+---------+-------------------+
2 rows in set

Upvotes: 2

Johan
Johan

Reputation: 929

I think this query is independent of any ordering in id's and any possible duplicate entries:

SELECT  left_id
FROM    `test`
WHERE   right_id IN (90,91,93,94,95)
GROUP BY left_id
HAVING  COUNT(DISTINCT right_id) = 5
UNION 
SELECT  left_id
FROM    `test`
WHERE   right_id IN (90,92,93,94,95)
GROUP BY left_id
HAVING   COUNT(DISTINCT right_id) = 5

Upvotes: 1

rocks
rocks

Reputation: 190

Try this

select left_id, group_concat(right_id) value
from test 
group by 1
having value = ('90,91,93,94,95')
   or value = ('90,92,93,94,95')

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

SELECT a.* -- [OR DISTINCT a.left_id] 
  FROM test a
  JOIN
     ( SELECT x.left_id 
         FROM test x 
        WHERE x.right_id IN(90,93,94,95) 
        GROUP 
           BY x.left_id HAVING COUNT(*) = 4
     ) b
    ON b.left_id = a.left_id
 WHERE a.right_id IN (91,92);

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

I think you could use the property of group_concat and check if the result is the same of the string you provided .. 

select distinct left_id, group_concat(right_id) from test 
having group_concat(right_id) = ('90,91,93,94,95')
   or group_concat(right_id) = ('90,92,93,94,95') 
group_by left_id

eventually adapt the format of string to the formatted resul fo group_concat

Upvotes: 0

Related Questions