IF Statement Within Query

[TheUkSniper]

Hello,

 

Never had to use a IF query before in this way so not sure if its possible.

 

My Query is

SELECT `CID`, `CSID`, `SCode`, `LName`, `IName`, `PCash`, `PDirect`, `CStart`, `CEnd`

FROM SA_Subscriptions

LEFT JOIN `SA_Sessions` ON `SA_Sessions`.`SUID` = `SA_Subscriptions`.`CSID`

LEFT JOIN `SA_Lessons` ON `SA_Lessons`.`LUID` = `SA_Sessions`.`SLesson`

LEFT JOIN `SA_Instructors` ON `SA_Instructors`.`IUID` = `SA_Sessions`.`SInstructor`

LEFT JOIN `SA_Prices` ON `SA_Prices`.`PRef` = `SA_Lessons`.`LPRate1` WHERE `CUID` = 2927

AND (`SA_Subscriptions`.`CEnd` >= CURDATE() OR `SA_Subscriptions`.`CEnd` = '0000-00-00')

AND (`SA_Prices`.`PEnd` >= `SA_Subscriptions`.`CStart` AND `SA_Prices`.`PStart` <= `SA_Subscriptions`.`CStart`)

 

What I want to do is if `SA_Subscriptions`.`CCat` = 0, I want to join SA_Prices To SA_Lessons on column LPRate1.

If it does not equal 0, I want the join to be on comum LPRate2.

 

Is this possible to do?

 

Kind Regards,

 

Alex

[kickstart]

Hi

 

Never tried such syntax, although it would make sense, and don't think it is supported in mysql.

 

Equivalent can be done using UNION

 

SELECT CID, CSID, SCode, LName, IName, PCash, PDirect, CStart, CEnd
FROM SA_Subscriptions 
LEFT JOIN SA_Sessions ON SA_Sessions.SUID = SA_Subscriptions.CSID 
LEFT JOIN SA_Lessons ON SA_Lessons.LUID = SA_Sessions.SLesson 
LEFT JOIN SA_Instructors ON SA_Instructors.IUID = SA_Sessions.SInstructor 
LEFT JOIN SA_Prices ON SA_Prices.PRef = SA_Lessons.LPRate1 
WHERE CUID = 2927 
AND SA_Subscriptions.CCat = 0
AND (SA_Subscriptions.CEnd >= CURDATE() OR SA_Subscriptions.CEnd = '0000-00-00') 
AND (SA_Prices.PEnd >= SA_Subscriptions.CStart AND SA_Prices.PStart <= SA_Subscriptions.CStart)
UNION
SELECT CID, CSID, SCode, LName, IName, PCash, PDirect, CStart, CEnd
FROM SA_Subscriptions 
LEFT JOIN SA_Sessions ON SA_Sessions.SUID = SA_Subscriptions.CSID 
LEFT JOIN SA_Lessons ON SA_Lessons.LUID = SA_Sessions.SLesson 
LEFT JOIN SA_Instructors ON SA_Instructors.IUID = SA_Sessions.SInstructor 
LEFT JOIN SA_Prices ON SA_Prices.PRef = SA_Lessons.LPRate2 
WHERE CUID = 2927 
AND SA_Subscriptions.CCat != 0
AND (SA_Subscriptions.CEnd >= CURDATE() OR SA_Subscriptions.CEnd = '0000-00-00') 
AND (SA_Prices.PEnd >= SA_Subscriptions.CStart AND SA_Prices.PStart <= SA_Subscriptions.CStart)

 

All the best

 

Keith

[kickstart]

Hi

 

Or without using union but instead joining on both and using IF in the columns selected to determine which tables columns to bring back

 

SELECT CID, CSID, SCode, LName, IName, if(SA_Subscriptions.CCat = 0,SA_Prices1.PCash,SA_Prices2.PCash), if(SA_Subscriptions.CCat = 0,SA_Prices1.PDirect,SA_Prices2.PDirect), CStart, CEnd
FROM SA_Subscriptions 
LEFT JOIN SA_Sessions ON SA_Sessions.SUID = SA_Subscriptions.CSID 
LEFT JOIN SA_Lessons ON SA_Lessons.LUID = SA_Sessions.SLesson 
LEFT JOIN SA_Instructors ON SA_Instructors.IUID = SA_Sessions.SInstructor 
LEFT JOIN SA_Prices SA_Prices1 ON SA_Prices.PRef = SA_Lessons.LPRate1 
LEFT JOIN SA_Prices SA_Prices2 ON SA_Prices.PRef = SA_Lessons.LPRate2 
WHERE CUID = 2927 
AND (SA_Subscriptions.CEnd >= CURDATE() OR SA_Subscriptions.CEnd = '0000-00-00') 
AND (SA_Prices.PEnd >= SA_Subscriptions.CStart AND SA_Prices.PStart <= SA_Subscriptions.CStart)

 

All the best

 

Keith

[TheUkSniper]

Hi Keith,

 

That works perfectly in both ways

 

Thank you for your help!

 

Kind Regards,