[thelist] [mysql] select query

[Joshua Olson]

> -----Original Message-----
> From: kris burford
> Sent: Thursday, June 10, 2004 7:33 AM
>
> i'm matching a buyer and seller, and one of the checks is to see whether
> either want only to "meet up" (i.e. *not* post their tickets/cheque). if
> this is the case, then their location has to match. so my sql statement
> includes
>
> SELECT blah blah blah
> WHERE (seller has the tickets that buyer wants) and CASE WHEN
> (s.exchange =
> 1 or b.exchange = 1) THEN (s.location = b.location) END
> ORDER by s.seller_entry_date, b.buyer_entry_date
> LIMIT 1

Kris,

Perhaps the CASE clause isn't necessary:

SELECT blah blah blah
WHERE (seller has the tickets that buyer wants)
  AND
  (
    (s.exchange = 0 AND b.exchange = 0)
    OR s.location = b.location
  )
ORDER by s.seller_entry_date, b.buyer_entry_date
LIMIT 1

I'm going to presume that when exchange = 0 they want to make an online or
in-person transaction, and when exchange = 1 they want to meet in person
only.  Is this correct?  If so, then essentially either value of exchange
being 1 is a veto to the online transaction entirely.

So, in this case the query returns the row if neither party have vetoed the
online transaction OR if the locations match (because if the locations match
the veto doesn't matter).

Does this give you what you want?

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168