Selecting from 2 tables in a single query

Question

I have a table that I'm querying for value 43 in the second field and I return the value of the third field

SELECT t1_field3 FROM table1 WHERE t1_field2=43

this returns 19, 39,73 

t1_id    t1_field2    t1_field3
-----    ---------    ---------
1           43        19////            
2           43        39////             
3           43        73////
4           73        43
5           13        40

Then I separately query a second table for additional information

SELECT * FROM table2 WHERE t2_id=t1_field3

t2_id    t2_field2    t2_field3
-----    ---------    ---------
19       value19.2    value19.3
39       value39.2    value39.3
73       value73.2    value73.3

Is there a way I could combine both table1 and table2 in the same query?

Answer 1

You're describing a JOIN. In this case you don't need to explicitly use the JOIN keyword though, you can just do this:

SELECT table1.t1_field3, table2.* FROM table1, table2 WHERE table1.t1_field2=43 AND table2.t2_id = table1.t1_field3

It would probably be helpful to learn about the different types of joins at some point; Coding Horror has a good post about it

Answer 2

select * from table2 
where t2_id in (select t1_field3 from table1 where t1_field2=43 )

Answer 3

There's a way to express the join directly as well like so:

select table1.t1_field3, table2.* 
  from table1
  join table2 on table1.t1_field3 = table2.t2_id
 where table1.t1_field2 = 43;