# Self Cross Join? **URL:** \ **Category:** Rails\ **Created:** [June 17, 2010, 4:22pm UTC](https://www.ruby-forum.com/t/self-cross-join/190300 "2010-06-17T16:22:44Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\ **Post date:** [June 17, 2010, 4:22pm UTC](https://www.ruby-forum.com/t/self-cross-join/190300/1 "2010-06-17T16:22:44Z")
I have a table with an integer column. I want to retrieve pairs of records where this integer sums to a total. For example: 4|red 6|blue 2|yellow If I want pairs that add up to 8 I should get [[red,red],[blue,yellow], [yellow,blue]]. Writing the query myself I would just use a join with no ON clause (I think this is a cross join): SELECT a.color, b.color FROM mytable a INNER JOIN mytable b WHERE a.value + b.value = 8 This works just fine from a sqlite console, but when I try to use find\_by\_sql I can’t get all of the information. It attempts to fit everything into an instance of the model class, which means that I only see b.color. What is the ‘rails’ way of doing what I am trying to accomplish? Is there a set of options I can use with find to recreate this query and get both colors out? I’m hoping for an array of 2-element arrays. ---
**Author:** ![brian](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/97f17d/32.png) [@brian](https://www.ruby-forum.com/u/brian)\ **Post date:** [June 17, 2010, 4:22pm UTC](https://www.ruby-forum.com/t/self-cross-join/190300/2 "2010-06-17T16:22:48Z")
On Jun 12, 6:48 pm, Brian [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > get both colors out? I’m hoping for an array of 2-element arrays. > You’ll need to alias one of them (ie b.color as something\_else). If > what you get back doesn’t really correspond to an instance of your > class then using connectiion.select\_all might be a better fit Fred.