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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/lists.cfm?link=t:6
Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:>
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
