That's awesome Mike, I'm not sure why I didn't think to do it like that (little bit of afternoon madness I guess). It makes a lot of sense.
Cheers, Cam On Wed, Feb 11, 2009 at 5:45 PM, Mike Cochrane <[email protected]>wrote: > Okay, that's not so clear with word wrapping. pastebin here: > http://paste.xcoder.co.nz/9 > > - Mike :-) > > > On 11/02/2009 17:41, Mike Cochrane wrote: > > Like this? > > SELECT > `Listing` . * > FROM > `Listing` > LEFT JOIN `CurrencyItem` ON `Listing`.`CurrencyID` =`CurrencyItem`.`ID` > WHERE > (`CurrencyItem`.`Name` = 'NZD' AND `Listing`.`ProductPrice` > '0' > AND `Listing`.`ProductPrice` < '10000') > OR > (`CurrencyItem`.`Name` = 'USD' AND `Listing`.`ProductPrice` > '0' > AND `Listing`.`ProductPrice` < '5348') > OR > (`CurrencyItem`.`Name` = 'AUD' AND `Listing`.`ProductPrice` > '0' > AND `Listing`.`ProductPrice` < '8004') > LIMIT 0 , 10 > > > > SELECT > `Listing` . * > FROM > `Listing` > LEFT JOIN `CurrencyItem` ON `Listing`.`CurrencyID` =`CurrencyItem`.`ID` > WHERE > `Listing`.`ProductPrice` > '0' > AND > ( > (`CurrencyItem`.`Name` = 'NZD' AND `Listing`.`ProductPrice` < > '10000') > OR > (`CurrencyItem`.`Name` = 'USD' AND `Listing`.`ProductPrice` < '5348') > OR > (`CurrencyItem`.`Name` = 'AUD' AND `Listing`.`ProductPrice` < '8004') > ) > LIMIT 0 , 10 > > - Mike > > On 11/02/2009 17:30, Cam Spiers wrote: > > > 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 > > > > > -- > Mike Cochrane > Web Team Leader > > gardyneHOLT - design partners > 18 Beresford Square Newton > PO Box 3340 Auckland New Zealand > p +64 9 300 3155 f +64 9 302 3349 m 021 545 565 > skype gardyneholt_mikecwww.gardyneholt.co.nz > > DISCLAIMER: This electronic message, together with any attachments, was > created solely for the use of the intended recipient(s), and may be > confidential. Views expressed may be those of the individual sender and are > not necessarily endorsed by gardyneHOLT. If you are not the intended > recipient, please advise us by return e-mail that you have received the > message in error and then delete it. Do not copy, disclose or use the > contents in any way. > > > > > --~--~---------~--~----~------------~-------~--~----~ NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected] -~----------~----~----~----~------~----~------~--~---
