Topic: Advanced query: Joining two tables and multiple selects
My problem involves two tables, Objects and Attributes.
|Object_ID (foreign key references Objects.id)
I want to be able to search for Objects by (potentially many) combinations of Attribute values. To show all of the unique Objects that have Attributes 'green' AND 'colorless', I am currently writing this mess of code:
SELECT DISTINCT objects.*
FROM attributes t1, attributes t2, objects
WHERE t1.val = 'green'
AND t2.val = 'colorless'
AND t1.object_id = t2.object_id
AND t1.object_id = objects.id
1. Is there a way to do this in SQL without making another table alias (e.g., t1 and t2) for every new conditional?
2. Does Rails facilitate anything like this by the :includes statement?
Thanks in advance,
Last edited by dustin (2007-04-30 01:22:56)