Argh

[AlaNio]

This has been driving me mad i cannot work out a way around it.
I have a table called properties and a table called history which has the propid as a foreign key. The history table stores any information about events that have happened on that property. The property can have several purchase events, one of which can be a purchase event. It also has a field called favouredpurchaser.

I'm trying to list the properties that have a purchase event but not a favoured purchaser. Because there can be several purchase events on one property a query simply checking the favouredpurchaser is not sufficient because a property could have a purchase even with a favouredpurchaser and without.

I have tried the following :

SELECT propid FROM properties INNER JOIN (SELECT history.propid FROM history WHERE event = 'purchase' AND favouredpurchaser = 0) AS sub ON sub.propid = properties.propid WHERE propid NOT IN (SELECT propid FROM history WHERE history.event='purchase' AND history.favouredpurchaser =1)

so select all the properties that don't have a favouredpurchaser, then make sure that there isnt another event in the same table where the property has a favouredpurchaser.

[chazzy]

try using a NOT IN between the full results, and those results that match having a favouredpurchaser.

[AlaNio]

is that not what i've done?

[chazzy]

it is, but try taking out the half of your query thats not doing that

[AlaNio]

sorry mate, i don't really know what you mean. I have tried testing each part of the query individually; these two select statements

SELECT history.propid FROM history WHERE event = 'purchase' AND favouredpurchaser = 0

SELECT history.propid FROM history WHERE event = 'purchase' AND favouredpurchaser = 1

and there are properties which are in both; its just a matter of returning the ones that are only in one and not the other.

[chazzy]

SELECT DISTINCT propid FROM history WHERE history.event='purchase' AND history.favouredpurchaser =1


Tells you all props that have a favouredpurchaser and are "purchases"


SELECT DISTINCT propid FROM history WHERE history.event='purchase'

Tell you all props that are "purchases"

This should then tell you all props that don't have favouredpurchaser , assuming i understand how your tables are structured.


SELECT DISTINCT propid FROM history WHERE history.event='purchase'
and propid not in (SELECT DISTINCT propid FROM history WHERE history.event='purchase' and favouredpurchaser=1)