Mysql select statment help

Some type of Join statement I think

[Sarah Atkinson]

I have 2 tables(tb1, tb2) in a db that have several colums in them
(tb1: a_id, b_id, col1, col2)
(tb2: b_id, col3, col4)
both a_id and b_id are unique
I'm creating a function that recived the "a_id". And in the function I want a select statment that uses the passed "a_id" to select the col3 and col4 from tb2 and col1 from tb1 with the "b_id being the link between the two.

I would like to do this in one statment but I supose I can use two.

also how would I then get the info with my $row=mysql_fetch_array($results)

[eelixduppy]

I'm not sure if this is what you mean...

$query = "SELECT tb1.col1,tb2.col3,tb2.col4 WHERE tb1.a_id = $a_id AND tb1.b_id = tb2.b_id";

Hope this helps!

eelix

[coopster]

The only thing you forgot there is the JOIN:

$query = "SELECT 
 tb1.col1, 
 tb2.col3, 
 tb2.col4 
 FROM tb1 
 INNER JOIN tb2 ON (tb1.b_id = tb2.b_id) 
 WHERE tb1.a_id = $a_id 
";

This query would skip any rows in the first table that have no corresponding rows in the second table. If you want ALL rows from the first table, even if they did not have corresponding rows in the second table, change the join from an INNER JOIN to a LEFT JOIN and all the columns in the second table will return NULL values. Example and discussion shown here:
LEFT JOIN versus INNER JOIN [webmasterworld.com]

There is a thread in our PHP Forum Library [webmasterworld.com] that discusses the Basics of extracting data from MySQL [webmasterworld.com] that show how to process the returned result set.

[Sarah Atkinson]

I finaly figured it out. I actualy eneded up using 4 tables instead of just 2. . It was one very long statement.

and article on Using MYSQL, Part 5 was what did the trick for me. I still have a lot of stuff to figure out on this function.