CODEWITHSUNDEEP
mysqlif-statementwhere-clause
xQp
xQp

Reputation: 302

MySQL Query WHERE -> IF

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?

Upvotes: 1

Views: 151

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

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

Upvotes: 2

Related Questions