# Sql server & linux: left join problem

**URL:** <https://www.ruby-forum.com/t/sql-server-linux-left-join-problem/52022>\
**Category:** Rails\
**Created:** [January 10, 2006, 5:45am UTC](https://www.ruby-forum.com/t/sql-server-linux-left-join-problem/52022 "2006-01-10T05:45:45Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![jamieorc](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f9ae1b/32.png) [@jamieorc](https://www.ruby-forum.com/u/jamieorc)\
**Post date:** [January 10, 2006, 5:45am UTC](https://www.ruby-forum.com/t/sql-server-linux-left-join-problem/52022/1 "2006-01-10T05:45:45Z")

</div>

I’ve got a left join problem of some sort connecting from linux  
FreeTDS/ODBC to SQL Server. It’s similar to the false post I had  
earlier, but this is a real problem. Here’s my code:

def find\_recipes\_for\_workorder  
@recipe\_pages, @recipes = paginate\_with\_sort :recipes, :per\_page  
=\> 10,   
:joins =\> ‘left join workorder on workorder.workorder\_id =  
recipe.workorder\_id’,   
:conditions =\> [“workorder.name = ?”, params[:workorder][:name]]  
render :template =\> ‘recipe/list’  
end

(paginate\_with\_sort at bottom of email.)

Here’s the top of the error:

Processing RecipeController#find\_recipes\_for\_workorder (for  
172.17.1.102 at 2006-01-09 23:26:30) [POST]  
Parameters: {“workorder”=\>{“name”=\>“RGL004”},  
“action”=\>“find\_recipes\_for\_workorder”, “controller”=\>“recipe”}

DBI::DatabaseError ( (8156) [unixODBC][FreeTDS][SQL Server]The column  
‘name’ was specified multiple times for ‘tally’.):  
/usr/lib/site\_ruby/1.8/DBD/ODBC/ODBC.rb:168:in `execute' /usr/lib/site_ruby/1.8/dbi/dbi.rb:640:in`execute’  
/usr/lib/site\_ruby/1.8/dbi/dbi.rb:671:in `select_all' /vendor/rails/activerecord/lib/active_record/connection_adapters/ sqlserver_adapter.rb:354:in`add\_limit\_offset!’  
/vendor/rails/activerecord/lib/active\_record/base.rb:931:in  
`add_limit!' /vendor/rails/activerecord/lib/active_record/base.rb:924:in`construct\_finder\_sql’  
/vendor/rails/activerecord/lib/active\_record/base.rb:395:in `find' /vendor/rails/actionpack/lib/action_controller/pagination.rb: 174:in`find\_collection\_for\_pagination’  
/vendor/rails/actionpack/lib/action\_controller/pagination.rb:  
192:in `paginator_and_collection_for' /vendor/rails/actionpack/lib/action_controller/pagination.rb: 124:in`paginate’  
/lib/sorting.rb:7:in `paginate\_with\_sort’

Any ideas appreciated.

Thanks,  
Jamie

* * *

class ActionController::Base  
def paginate\_with\_sort(collection\_id, options={})  
@params[:sort\_direction] ||= ‘-1’  
asc\_desc = {‘1’ =\> “asc”, ‘-1’ =\> “desc”}  
options[:order] = “#{@params[:sort\_by]} #{asc\_desc[@params  
[:sort\_direction]]}” if @params[:sort\_by]  
@params[:sort\_direction] = @params[:sort\_direction].to\_i \* (-1)  
paginate collection\_id, options  
end  
end

---

<div class="post-metadata">

**Author:** ![jamieorc](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f9ae1b/32.png) [@jamieorc](https://www.ruby-forum.com/u/jamieorc)\
**Post date:** [January 10, 2006, 6:08pm UTC](https://www.ruby-forum.com/t/sql-server-linux-left-join-problem/52022/2 "2006-01-10T18:08:45Z")

</div>

It turns out that SQLServerAdaptor’s add\_limit\_offset has a bug in  
it. If there is a left join on two tables that have columns with the  
_same name_, the error below is created. I’ve been able to verify  
this by taking the generated sql statement and placing it into Query  
Analyzer.

This is the sort of statement generated:  
SELECT count(\*) as TotalRows from  
(SELECT TOP 1000000000 \* FROM recipe  
left join workorder on workorder.workorder\_id = recipe.workorder\_id  
WHERE (workorder.name = ‘VMD001’) ) tally

recipe and workorder both have “name” as a column, so the query  
throws the error  
“DBI::DatabaseError ( (8156) [unixODBC][FreeTDS][SQL Server]The  
column ‘name’ was specified multiple times for ‘tally’.):”

I’m wondering if anyone has already patched this, but not submitted  
it to trac.

I’ll be digging in and see if I can create a patch for this today. If  
anyone has anything done already, that would be great.

Jamie