Fetch data from multiple MySQL tables

Question

My two tables look like this:

       TABLE1                      TABLE2
+--------------------+      +--------------------+
|field1|field2|field3|  and |field2|field4|field5|
+--------------------+      +--------------------+

I am already running a SELECT query for TABLE1, and assorting all of the data into variables:

$query = "SELECT * FROM TABLE1 WHERE field2 = 2";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);    
  if((!is_bool($result) || $result) && $num_rows) {
   while($row = mysql_fetch_array($result))
    {
   $field1 = $row['field1'];
   $field2 = $row['field2'];
   $field3 = $row['field3'];
    }
  }

What I want to do is get the data from 'field4' on TABLE2 and add it to my variables. I would want to get field4 WHERE field2 = 2

Answer 1

http://en.wikipedia.org/wiki/Join_%28SQL%29

Answer 2

You need to use a JOIN:

SELECT TABLE1.*,TABLE2.field4 FROM TABLE1 JOIN TABLE2 ON TABLE1.field2=TABLE2.field2 WHERE TABLE1.field2=2;

Answer 3

SELECT
  t1.*,
  t2.field4
FROM
  TABLE1 AS t1,
  TABLE2 AS t2
WHERE 
    t1.field2 = 2
  AND
    t1.field2 = t2.field2

You want to join the two table which can be done explicitly (using the JOIN operator or one of its variants) our implicitly (by SELECTing from multiple tables directly).

From your description, I suppose that you want to do the join where field2 in the two tables have the same value (2).

---

If TABLE2 will not always provide a valid join candidate but you still want the data from TABLE1, you should use a LEFT JOIN (giving NULL for field4 where nothing matched):

SELECT
  t1.*,
  t2.field4
FROM
    TABLE1 AS t1
  LEFT JOIN
    TABLE2 AS t2
  ON
    t1.field2 = t2.field2
WHERE 
  t1.field2 = 2

Answer 4

hi try the following as ur query.

It is not preferred all the time to use * in the select query

SELECT T1.field1,T1.field2,T1.field3, T2.field4 FROM TABLE1 AS T1 INNER JOIN TABLE2 AS T2 ON T1.field2=T2.field2 WHERE field2 = 2