IF in WHERE

IF in WHERE

Post by Cam Spier » Thu, 12 Feb 2009 13:24:09


Hi I was wondering if this type of query is possible?

SELECT `Listing` . *
FROM `Listing`
LEFT JOIN `CurrencyItem` ON `Listing`.`CurrencyID` =
`CurrencyItem`.`ID`
WHERE
IF `CurrencyItem`.`Name` = 'NZD'
THEN `Listing`.`ProductPrice` > '0'
AND `Listing`.`ProductPrice` < '10000'
ELSEIF `CurrencyItem`.`Name` = 'USD'
THEN `Listing`.`ProductPrice` > '0'
AND `Listing`.`ProductPrice` < '5348'
ELSEIF `CurrencyItem`.`Name` = 'AUD'
THEN `Listing`.`ProductPrice` > '0'
AND `Listing`.`ProductPrice` < '8004'
END IF
LIMIT 0 , 10

And if not would there be any query I could dynamically generate which
achieved the same result?

Cheers,
Cam
 
 
 

IF in WHERE

Post by varo G. Vi » Thu, 12 Feb 2009 17:19:00

Cam Spiers escribi >> Hi I was wondering if this type of query is possible? >> >> SELECT `Listing` . * >> FROM `Listing` >> LEFT JOIN `CurrencyItem` ON `Listing`.`CurrencyID` = >> `CurrencyItem`.`ID` >> WHERE >> IF `CurrencyItem`.`Name` = 'NZD' >> THEN `Listing`.`ProductPrice`>> '0' >> AND `Listing`.`ProductPrice`<< '10000' >> ELSEIF `CurrencyItem`.`Name` = 'USD' >> THEN `Listing`.`ProductPrice`>> '0' >> AND `Listing`.`ProductPrice`<< '5348' >> ELSEIF `CurrencyItem`.`Name` = 'AUD' >> THEN `Listing`.`ProductPrice`>> '0' >> AND `Listing`.`ProductPrice`<< '8004' >> END IF >> LIMIT 0 , 10 >> >> And if not would there be any query I could dynamically generate which >> achieved the same result?

You can't do such thing as far as I know. The closest I can think of is
using CASE statements to generate the values:

WHERE
Listing`.`ProductPrice`>> CASE
WHEN CurrencyItem`.`Name` = 'NZD'
THEN '0'
WHEN `CurrencyItem`.`Name` = 'USD'
THEN '5348'
WHEN `CurrencyItem`.`Name` = 'AUD'
THEN '0'
ELSE 'Whatever'
END AND Listing`.`ProductPrice`<< CASE
.......
END


--
-- http://www.yqcomputer.com/ - varo G. Vicario - Burgos, Spain
-- Mi sitio sobre programaci web: http://www.yqcomputer.com/
-- Mi web de humor al ba Mar: http://www.yqcomputer.com/

 
 
 

IF in WHERE

Post by Mark Huize » Thu, 12 Feb 2009 17:32:13

The wise Cam Spiers enlightened me with:

Try it and you'll see :-)

What is the difference between this and

(CurrencyItem.Name='NZD' and
Listing.ProductPrice>0 and Listing.ProductPrice<10000)
OR
....

?

Mark
 
 
 

IF in WHERE

Post by Captain Pa » Thu, 12 Feb 2009 18:27:02


As Mark has pointed out, there is no need for this, you construct such
logic using normal boolean algebra
 
 
 

IF in WHERE

Post by varo G. Vi » Thu, 12 Feb 2009 20:47:46

Mark Huizer escribi >> Try it and you'll see :-) >>> SELECT `Listing` . * >>> FROM `Listing` >>> LEFT JOIN `CurrencyItem` ON `Listing`.`CurrencyID` = >>> `CurrencyItem`.`ID` >>> WHERE >>> IF `CurrencyItem`.`Name` = 'NZD' >>> THEN `Listing`.`ProductPrice`>> '0' >>> AND `Listing`.`ProductPrice`<< '10000' >>> ELSEIF `CurrencyItem`.`Name` = 'USD' >>> THEN `Listing`.`ProductPrice`>> '0' >>> AND `Listing`.`ProductPrice`<< '5348' >>> ELSEIF `CurrencyItem`.`Name` = 'AUD' >>> THEN `Listing`.`ProductPrice`>> '0' >>> AND `Listing`.`ProductPrice`<< '8004' >>> END IF >>> LIMIT 0 , 10 >>> >>> And if not would there be any query I could dynamically generate which >>> achieved the same result? >> >> What is the difference between this and >> >> (CurrencyItem.Name='NZD' and >> Listing.ProductPric>>0 and Listing.ProductPric<<10000) >> OR

Of course, this is the approach that makes sense, not mine...



--
-- http://www.yqcomputer.com/ - varo G. Vicario - Burgos, Spain
-- Mi sitio sobre programaci web: http://www.yqcomputer.com/
-- Mi web de humor al ba Mar: http://www.yqcomputer.com/
 
 
 

IF in WHERE

Post by Cam Spier » Fri, 13 Feb 2009 04:05:34

On Feb 12, 12:47m, "varo G. Vicario"

> Of course, this is the approach that makes sense, not mine..>
> ->
> -- http://www.yqcomputer.com/ varo G. Vicario - Burgos, Spai>
> -- Mi sitio sobre programaci web: http://www.yqcomputer.com/ >
> -- Mi web de humor al ba Mar: http://www.yqcomputer.com/ >
> --

Yeah thanks heaps guys, I got it. Don't know why I didn't think of
just doing it that way, think I was trying to think of too complex a
solution...

Cheers
 
 
 

IF in WHERE

Post by ThanksButN » Fri, 13 Feb 2009 05:23:36


You just need to put away your Procedural Language Brain
for a moment and use your Relational SQL Brain in its place!

SQL is not a Fancy Flavored Fortran!

As you come to learn more and more different languages, C,
Java, Python, etc., you come to learn that the more things
change the more they stay the same. You pretty much handle
all of those languages the same way.

Not SQL! Now you need to use a totally different mindset.

Don't worry, like anything else, it becomes second nature
after a little practice.

/:-/