[cfaussie] Re: OT: sql question

Spike Sun, 07 Sep 2003 20:32:14 -0700

Have you tried it with an OR clause?

select c.client_id, cc.client_class, cd.first_name + ' ' + cd.last_name as full_name, from client c on cp.client_id = c.client_id join contact_detail cd on c.contact_detail_1 = cd.contact_detail_id join client_class cc on c.client_class_id = cc.client_class_id join baseProperty p on p.propertyID = 12 join residential r on p.propertyID = r.propertyID where p.agentID = 1 and (p.propSuburub in (select s.suburb from suburb s where s.suburbid in (select bcs.suburb_id from buying_criteria_suburb bcs where bcs.buying_criteria_id = bc.buying_criteria_id)) OR (select count(s.suburb) from suburb s where s.suburbid in (select bcs.suburb_id from buying_criteria_suburb bcs where bcs.buying_criteria_id = bc.buying_criteria_id))) = 0)

What database are you using?

Spike

Toby Tremayne wrote:

Hi all,

  here's a curly one for you - I'm trying to find buyers in a
  database whose criteria matches a property's (as in house)
  features.  Property has numberBeds, numberBaths etc and buyer
  criteria has bed_min, bed_max etc etc.  That much is all easy...

  the difficult part is that the buyer criteria record is linked to
  suburbs via a matrix table, so there's potentially a list of suburb
  ids that pertain to the buyer criteria (IE only houses in these
  suburbs).

  this is what I started with before I realised sometime the suburb
  sub query comes back empty:

select  c.client_id,
               cc.client_class,
               cd.first_name + ' ' + cd.last_name as full_name,
       from    client c on cp.client_id = c.client_id
       join    contact_detail cd on c.contact_detail_1 = cd.contact_detail_id
       join    client_class cc on c.client_class_id = cc.client_class_id
       join    baseProperty p on p.propertyID = 12
       join    residential r on p.propertyID = r.propertyID
       where   p.agentID = 1
       and     p.propSuburub in (select s.suburb from suburb s where s.suburbid in 
(select bcs.suburb_id
from buying_criteria_suburb bcs where bcs.buying_criteria_id = bc.buying_criteria_id))
       ....etc

so how can I translate the following into sql:

       if the buyer suburbs subquery comes back empty, don't bother
       adding a where clause, otherwise make sure the property's
       suburb is in the list of buyer criteria suburbs.

any advice at all would be most appreciated...

cheers,
Toby

--------------------------------

      Life is Poetry,
          write it in your own words

--------------------------------

Toby Tremayne
Cold Fusion Developer
Code Poet and Zen Master of the Heavy Sleep
Virtual Tours
+61 416 048 090
ICQ: 13107913


---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]

MX Downunder AsiaPac DevCon - http://mxdu.com/


--
Stephen Milligan
Consultant for hire
http://www.spike.org.uk

---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]

MX Downunder AsiaPac DevCon - http://mxdu.com/

[cfaussie] Re: OT: sql question

Reply via email to