Database efficiency question

[zymurgy]

Hello,

I have a problem that has been irking me for quite some time. Basically, what I'm trying to do is retrieve ids from one table, then get data corresponding to that id from another table.

table 1
id
1
2
3

table 2
id, field 1, field 2, field 3, field 4, field 5 ... field n
1, ----------------
1, ----------------
1, ----------------
2, ----------------
2, ----------------
3, ----------------
3, ----------------

The only way I could think of to do it was nested for loops...
for 1 - 3, get first id {
  for 1 - n, get all the data for all the stuff in table 2 with the corresponding id {
    do a bunch of stuff
  }
}

As you can see, this is very inefficient (O(n^2)), and the script just times out after 120 seconds. I know there is a more efficient way to do it using sql commands rather than nested for loops, but unfortunately I haven't yet been able to figure it out. Any help would be greatly appreciated.

Thanks a lot.

[toplay]

A basic join (or left join depending on what you need) will do the trick. Example:

SELECT
      t1.*                # or list just the columns you want
    , t2.*                # or list just the columns you want
FROM
    `table1_name` t1
JOIN
    `table2_name` t2
USING
    (id)
;

That's it really. Without a WHERE clause you'll get all rows back that the id matches in both tables.

If the column name is not the same in both tables then replace my USING (id) with this:

ON
    (t1.column_name = t2.column_name)

hth.