# Joining tables and query design advice

**URL:** <https://www.ruby-forum.com/t/joining-tables-and-query-design-advice/103044>\
**Category:** Rails\
**Created:** [May 1, 2007, 4:57am UTC](https://www.ruby-forum.com/t/joining-tables-and-query-design-advice/103044 "2007-05-01T04:57:05Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![dustin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/5daacb/32.png) [@dustin](https://www.ruby-forum.com/u/dustin)\
**Post date:** [May 1, 2007, 4:57am UTC](https://www.ruby-forum.com/t/joining-tables-and-query-design-advice/103044/1 "2007-05-01T04:57:05Z")

</div>

Hi everyone,

My problem involves two tables, Objects and Attributes:

## Objects

|ID

| Name |
| --- |

## Attributes

|ID  
|Object\_ID (foreign key references Objects.id)

| Value |
| --- |

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

Is there a way I can search make queries like (‘Show me all of the  
records that have ALL of these attributes’) and just feed it a list of  
attributes?

The technique I am using requires that I make a new table alias for each  
new attribute. Does rails have some built-in join mechanism to do  
queries like this?

Thanks in advance,  
Dustin