Hi Toby,
Split it into two separate queries.
The first one being
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))
Put the results in a comma delimited list.
In the main query, have something like
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
<cfif the list has something in it>
and p.propSuburub in (#the list#)
</cfif>
Cheers,
Phil.
----- Original Message -----
From: "Toby Tremayne" <[EMAIL PROTECTED]>
To: "CFAussie Mailing List" <[EMAIL PROTECTED]>
Sent: Monday, September 08, 2003 1:24 PM
Subject: [cfaussie] OT: sql question
> 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/
>
---
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/
