Developer Forum

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

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/

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

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

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/

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

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.

/:-/