CODEWITHSUNDEEP
mysql
Muchtar
Muchtar

Reputation: 122

GROUP column where other column condition with AND

I'm not the man of words, but I think the following example will help to show what I want:

When I execute this:

SELECT      schritt5_id,
            liste_id                
FROM        schritt5    
JOIN        els_fehler_zo
ON          els_fehler_zo.schritt5_id = schritt5.id        
JOIN        liste
ON          liste.id = els_fehler_zo.liste_id    
WHERE       els_fehler_zo.liste_id  = 1
      OR    els_fehler_zo.liste_id  = 8    
ORDER BY    schritt5_id ASC;

I get this:

schritt5_id | liste_id
3             1
3             8
6             8
11            8
15            8
25            1
28            1
420           1
421           8
422           1
422           8
423           1
423           8
426           8
438           1

Now I want to select all the schritt5_id rows, which have the liste_id 8 AND 1. I was trying to keep it simple and changed the OR with an AND but this is wrong. Now I've changed the query to this:

SELECT      schritt5_id,
            liste_id,
            GROUP_CONCAT(liste.id ORDER BY liste.id SEPARATOR ' | ') AS cellValue          
FROM        schritt5
JOIN        els_fehler_zo
ON          els_fehler_zo.schritt5_id = schritt5.id    
JOIN        liste
ON          liste.id = els_fehler_zo.liste_id
WHERE       els_fehler_zo.liste_id  = 1
      OR    els_fehler_zo.liste_id  = 8
GROUP BY    schritt5_id
ORDER BY    schritt5_id ASC;

And the result is this: 

schritt5_id  |  liste_id  | cellValue
3              1               1 | 8
6              8               8
11             8               8
15             8               8
25             1               1
28             1               1
420            1               1
421            8               8
422            1               1 | 8
423            1               1 | 8
426            8               8
438            1               1

What I actually want to get is this:

schritt5_id  |  liste_id  | cellValue
3              1               1 | 8
422            1               1 | 8
423            1               1 | 8

When I Join the table with it self like this:

SELECT    *
FROM      els_fehler_zo
JOIN      els_fehler_zo a
ON        a.schritt5_id = els_fehler_zo.schritt5_id
      AND a.liste_id   != els_fehler_zo.liste_id
WHERE       a.liste_id    = 1
      AND  els_fehler_zo.liste_id    = 8        
      OR   els_fehler_zo.liste_id    = 8
      AND  a.liste_id    = 1       
GROUP BY  a.schritt5_id;

Is there a other way to do it, without joining the table with it self. Because, if I want to search for liste_id 8 AND 1 AND 2 AND 3 ans so on... that means I have to join it every time with it self.

Upvotes: 1

Views: 51

Answers (1)

Sookie Singh
Sookie Singh

Reputation: 1623

We can use HAVING clause. HAVING clause is used to restrict the results returned by the GROUP BY clause. WHERE is applied before GROUP BY, HAVING is applied after (and can filter on aggregates).

HAVING COUNT(*) >= 2 will return rows with count greater than equal to 2 which you were asking for. 

SELECT      schritt5_id,
            liste_id,
            GROUP_CONCAT(liste.id ORDER BY liste.id SEPARATOR ' | ') AS cellValue          
FROM        schritt5
JOIN        els_fehler_zo
ON          els_fehler_zo.schritt5_id = schritt5.id    
JOIN        liste
ON          liste.id = els_fehler_zo.liste_id
WHERE       els_fehler_zo.liste_id  = 1
      OR    els_fehler_zo.liste_id  = 8
GROUP BY    schritt5_id
HAVING COUNT(*) >= 2 
ORDER BY    schritt5_id ASC;

A Sample fiddle: http://sqlfiddle.com/#!9/5d857/3

Upvotes: 1

Related Questions