Need help in SQL and Sequel involving an inner join and where / filter

Question

Need help transferring sql to continue:
SQL:

SELECT table_t.curr_id FROM table_t INNER JOIN table_c ON table_c.curr_id = table_t.curr_id INNER JOIN table_b ON table_b.bic = table_t.bic WHERE table_c.alpha_id = 'XXX' AND table_b.name='Foo'; 

I’m stuck in a sequel, I don’t know how to filter, for now, like this:

  cid= table_t.select(:curr_id). join(:table_c, :curr_id=>:curr_id). join(:table_b, :bic=>:bic). filter( ????? ) 

The answer to a better idiom, the higher, is also evaluated. Tnx.

UPDATE:
I need to change a little for it to work.

 cid = DB[:table_t].select(:table_t__curr_id). join(:table_c, :curr_id=>:curr_id). join(:table_b, :bic=>:table_t__bic). #add table_t or else ERROR: column table_c.bic does not exist filter(:table_c__alpha_id => 'XXX', :table_b__name => 'Foo') 

without a filter,

 cid = DB[:table_t].select(:table_t__curr_id). join(:table_c, :curr_id=>:curr_id, :alpha_id=>'XXX'). join(:table_b, :bic=>:table_t__bic, :name=>'Foo') 

btw I am using pgsql 9.0

Answer 1

This is the clean Sequel path:

 cid = DB[:table_t].select(:table_t__curr_id). join(:table_c, :curr_id=>:curr_id). join(:table_b, :bic=>:bic). filter(:table_c__alpha_id => 'XXX', :table_b__name => 'Foo') 

Please note that you can also do this without WHERE, since you are using INNER JOIN:

 cid = DB[:table_t].select(:table_t__curr_id). join(:table_c, :curr_id=>:curr_id, :alpha_id=>'XXX'). join(:table_b, :bic=>:bic, :name=>'Foo') 

Answer 2

I think you can always use something like

 .filter('table_c.alpha_id = ? AND table_b.name = ?', 'XXX', 'Foo') 

Answer 3

One thing to keep in mind is that Sequel is very happy to let you use raw SQL. If it will be easier for you to express your query as SQL continues, just be sure to comment it so that you can find it later. You can then go back to this line and adjust it to take advantage of Sequel awesomeness.

Try to avoid everything that is specific to a particular DBM, though, because you will reduce portability, which is one of the main reasons for using ORM to generate queries.