MySQL Query WHERE -> IF

Question

hope someone can help me with this,

im trying to connect a WHERE argument within an MySQL Statement with an IF operation, but i dont know how to handle it...

SELECT  k.EventId AS Id
    , ws.Name AS Name
    , dauer AS Dauer
    , kat.Name AS Kurztyp
    , doz.Name AS Dozent        
    , DATE_FORMAT(k.Begin, '%Y-%m-%d') AS Begin
    , COUNT(an.EventId) AS Teilnehmer
FROM kalender AS k
        LEFT JOIN workshop AS ws        
            ON (k.WorkshopId = ws.Id)
        LEFT JOIN angemeldet AS an      
            ON (k.EventId = an.EventId)
        LEFT JOIN dozent AS doz         
            ON (k.Dozent = doz.Id)
        LEFT JOIN ws_kategorie as kat   
            ON ((ws.Kategorie = kat.Id) or (ws.Kategorie2 = kat.Id))
        INNER JOIN bezahlvariante AS bezvar
                ON (an.bezahlvariante_idbezahlvariante = bezvar.idbezahlvariante)
    WHERE kat.Name LIKE '%security%'
        AND k.Begin > '2014-04-01'
        AND k.aktiv = '1'

---------------->

        AND bezvar.TN_Anzahl_phys = '1'

<----------------- this should be like: IF(COUNT(an.EventId)>0)
THAN bezvar.TN_Anzahl_phys = '1'
ELSE bezvar.TN_Anzahl_phys IN (1,2)

            GROUP BY k.EventId;

How can i use an IF operation like this in MySQL?

Abhik Chakraborty · Accepted Answer · 2014-05-08 10:35:29Z

2

You can use case-when as

GROUP BY k.EventId
having
case 
  when COUNT(an.EventId)>0 then bezvar.TN_Anzahl_phys = '1'
  else
   bezvar.TN_Anzahl_phys IN (1,2)
END

edited May 8, 2014 at 10:35

answered May 8, 2014 at 10:28

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Abhik Chakraborty Over a year ago

thats correct it will show invalid use of group let ne correct it

xQp Over a year ago

sadly doesnt work, SQL error Error Code: 1054. Unknown column 'bezvar.TN_Anzahl_phys' in 'having clause' idk why

xQp Over a year ago

DDL is way to large, only 8000 allowed but has about 20k? :/

Abhik Chakraborty Over a year ago

wow just have some sample data dont need all may be 10 15 rows.

Abhik Chakraborty Over a year ago

ouch but usually this kind of syntax should work, try removing the having clause and try to select the col bezvar.TN_Anzahl_phys in select and see if its working. If yes then it should work in the having clause, I just tested in my local mysql using same pattern of query by joining tables and it works well.

|

Collectives™ on Stack Overflow

MySQL Query WHERE -> IF

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related