# Missing values in LEFT OUTER JOIN using find\_by\_sql (possible bug?)

**URL:** <https://discuss.rubyonrails.org/t/missing-values-in-left-outer-join-using-find-by-sql-possible-bug/22447>\
**Category:** rubyonrails-talk\
**Created:** [December 31, 2007, 1:37pm UTC](https://discuss.rubyonrails.org/t/missing-values-in-left-outer-join-using-find-by-sql-possible-bug/22447 "2007-12-31T13:37:55Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![wmciver](https://avatars.discourse-cdn.com/v4/letter/w/cc9497/32.png) [@wmciver](https://discuss.rubyonrails.org/u/wmciver)\
**Post date:** [December 31, 2007, 1:37pm UTC](https://discuss.rubyonrails.org/t/missing-values-in-left-outer-join-using-find-by-sql-possible-bug/22447/1 "2007-12-31T13:37:55Z")

</div>

This is related to an earlier post " left outer join change in 1.2.1?" by Benjamin Ritcey.

I need to use a LEFT OUTER JOIN for its classic purpose: joining two two tables such that tuples are returned containing attribute values of the left hand table even if there is no join on the right hand table.

From the MySQL console, the following works as desired:

> SELECT afnu\_mca\_questions.\*, afnu\_mca\_responses.\*

&nbsp;&nbsp;&nbsp;&nbsp;-\> FROM afnu\_mca\_questions LEFT OUTER JOIN afnu\_mca\_responses &nbsp;&nbsp;&nbsp;&nbsp;-\> ON afnu\_mca\_questions.id = afnu\_mca\_responses.afnu\_mca\_question\_id &nbsp;&nbsp;&nbsp;&nbsp;-\> WHERE afnu\_mca\_questions.afnu\_assessment\_id = 1 AND &nbsp;&nbsp;&nbsp;&nbsp;-\> (afnu\_mca\_responses.exam\_id = 1 OR afnu\_mca\_responses.id IS NULL);

Attribute values for afnu\_mca\_questions.\* (left hand) are returned even if afnu\_mca\_responses.\* is NULL.

Reducing the attribute values that are projected to save space, I get the following in MySQL:

> SELECT afnu\_mca\_questions.id, afnu\_mca\_responses.id

&nbsp;&nbsp;&nbsp;&nbsp;-\> FROM afnu\_mca\_questions LEFT OUTER JOIN afnu\_mca\_responses &nbsp;&nbsp;&nbsp;&nbsp;-\> ON afnu\_mca\_questions.id = afnu\_mca\_responses.afnu\_mca\_question\_id &nbsp;&nbsp;&nbsp;&nbsp;-\> WHERE afnu\_mca\_questions.afnu\_assessment\_id = 1 AND &nbsp;&nbsp;&nbsp;&nbsp;-\> (afnu\_mca\_responses.exam\_id = 1 OR afnu\_mca\_responses.id IS NULL);

---

<div class="post-metadata">

**Author:** ![Mark\_James](https://avatars.discourse-cdn.com/v4/letter/m/58f4c7/32.png) [@Mark\_James](https://discuss.rubyonrails.org/u/Mark_James)\
**Post date:** [January 1, 2008, 3:49am UTC](https://discuss.rubyonrails.org/t/missing-values-in-left-outer-join-using-find-by-sql-possible-bug/22447/2 "2008-01-01T03:49:44Z")

</div>

wmciver@gmail.com wrote:

> &nbsp;&nbsp;&nbsp;@all\_question\_responses = &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AfnuMcaQuestion.find\_by\_sql(["SELECT afnu\_mca\_questions.id, " + &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" afnu\_mca\_responses.id " + &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"FROM afnu\_mca\_questions LEFT OUTER JOIN afnu\_mca\_responses " + &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"ON afnu\_mca\_questions.id = afnu\_mca\_responses.afnu\_mca\_question\_id " + &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"WHERE afnu\_mca\_questions.afnu\_assessment\_id = ? AND " + &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"(afnu\_mca\_responses.exam\_id = ? OR afnu\_mca\_responses.id IS NULL)", &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;@afnu\_assessment\_id, @exam\_id] &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)
> 
> The results of this query are the following:
> 
> Object: --- - !ruby/object:AfnuMcaQuestion &nbsp;&nbsp;attributes: &nbsp;&nbsp;&nbsp;&nbsp;id: "1" - !ruby/object:AfnuMcaQuestion &nbsp;&nbsp;attributes: &nbsp;&nbsp;&nbsp;&nbsp;id:
> 
> The attribute value for the second "id" should be "4".
> 
> Why is this happening? Is this a bug in find\_by\_sql?

The responses id is overwriting the questions id. Instead use

SELECT afnu\_mca\_questions.id, afnu\_mca\_responses.id as response\_id ...