more tricky sql [SOLVED]

hi everyone,

i’ve spent ages searching for a solution to my problem, but can’t find one.

I’ve got the following tables with the following columns:

property: id, address, number_of_bedrooms
attribute: id, title (e.g. washing machine, central heating, furnished)
property_attribute: property, attribute (composite primary key on both columns)

what i’m trying to do is select property ids that have all the attributes i’m looking for, for example, all those properties that have a washing machine and are furnished.

I’’ve been trying things like the following which aren’t working, probably because the attribute column won’t be equal to two different values at once.

SELECT property FROM property_attribute WHERE attribute=’washing machine’ AND attribute=’furnished’

i can’t think of how to do this since there will be varying numbers of attributes i need to search on.

does anyone know how i can do this?

thanks

A JOIN across the last 2 tables on the id fields should do the trick. Google MySQL JOIN for a bunch of good tutorials.

hi mark,

sorry, i missed out the JOIN. the sql i’ve been using so far should be more like:

SELECT pa.property FROM property_attribute AS pa INNER JOIN attribute AS a ON pa.attribute=a.id WHERE a.title=’washing machine’ AND a.title=’furnished’

this is returning an empty set because, i think, a.title can’t have two values at once. what i want to say in pseudo-sql is:

SELECT pa.property FROM property_attribute AS pa INNER JOIN attribute AS a ON pa.attribute=a.id WHERE
(pa.property=X AND a.title=’washing machine’) AND (pa.property=X AND a.title=’furnished’)

so the only results that will be returned are where a property has both attributes.

hmm... tricky...

and the answer is...

SELECT pa.property FROM property_attribute AS pa WHERE
pa.property=(SELECT property FROM property_attribute AS pa2 WHERE pa2.attribute=’4’) AND pa.property=(SELECT property FROM property_attribute AS pa2 WHERE pa2.attribute=’6’)

where in ’attribute’ we have:
4 = furnished
6 = washing machine

you could also do something like this



That should give you all of the properties with both attributes

thanks, i’ll try it. that’s more elegant, and will probably be more efficient.