CODEWITHSUNDEEP
sqlpostgresqljoin
Red
Red

Reputation: 2246

SQL Join using a join table from rails

So, I am not sure what the best way to do a join for these tables was. I want to use a JOIN because I believe it is faster than bring all three of the tables in on FROM. So, if i have three tables...

Table1
--id
--data

Table2
--id
--data

Table1_Table2
--table1_id
--table2_id

How can I do a join for this data using the join table?

Upvotes: 0

Views: 698

Answers (2)

vyegorov
vyegorov

Reputation: 22925

Mentioning all tables in the FROM clause is also a join, implicit one. It is not recommended to use it as it might lead to a Cartesian product of the tables involved in case you'll forget to add predicates in the WHERE clause.

Have a look on the Wikipedia JOIN article and also at this very nice blogpost about joins by Jeff Atwood.

I think that you're interested in the INNER JOIN between the tables, although other variants exists. Try this:

SELECT t1.*, t2.*
  FROM Table1 t1
  INNER JOIN Table1_Table2 tt ON t1.id = tt.table1_id
  INNER JOIN Table2 t2 ON t2.id = tt.table2_id;

I skipped Table1_Table2 columns from the select list, as there's nothing special there.

Upvotes: 2

Mark
Mark

Reputation: 891

While the above solution works, you might want to give the rails-way solution a try

Since you require a join table with only the primary keys of the participating tables, you can loop into has_and_belongs_to_many association. It does the same as the SQL query behind the scenes and is a neater solution. You can also look into has_many through association(also explained in the linked railscasts) if you want to add more columns to the join table.

Upvotes: 0

Related Questions