Hi Spike,
thanks for that - the or was the last thing I was trying, although
I was going the long way round and doing the same subselect in each
instead of the count (boot to the head). With a couple of syntax
changes the working query clause is this:
and (p.propSuburb 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
)
just had to add buying_criteria bc to the from clause and bob's
yer wossname. Ta all for the help.
cheers,
Toby
Monday, September 8, 2003, 1:31:28 PM, you wrote:
S> Have you tried it with an OR clause?
S> select c.client_id,
S> cc.client_class,
S> cd.first_name + ' ' + cd.last_name as full_name,
S> from client c on cp.client_id = c.client_id
S> join contact_detail cd on c.contact_detail_1 = cd.contact_detail_id
S> join client_class cc on c.client_class_id = cc.client_class_id
S> join baseProperty p on p.propertyID = 12
S> join residential r on p.propertyID = r.propertyID
S> where p.agentID = 1
S> and (p.propSuburub in (select s.suburb from suburb s where s.suburbid
in (select bcs.suburb_id
S> from buying_criteria_suburb bcs where bcs.buying_criteria_id =
bc.buying_criteria_id))
S> OR (select count(s.suburb) from suburb s where s.suburbid in (select
bcs.suburb_id
S> from buying_criteria_suburb bcs where bcs.buying_criteria_id =
bc.buying_criteria_id))) = 0)
S> What database are you using?
S> Spike
S> 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/
>>
>>
>>
>>
--------------------------------
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/
