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

This is a duplicate of http://stackoverflow.com/questions/2627083/php-fetch-rows-from-multiple-mysql-tables — nikc.org, Apr 13 '10 at 06:06

jensgram · Accepted Answer · 2010-04-13T06:02:20.460

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

edited Apr 13 '10 at 06:02

answered Apr 13 '10 at 05:56

jensgram

31,109
6
81
98

@Jon McIntosh You're welcome :) Please consider closing the question (by accepting an answer) if your problem is solved. – jensgram Apr 13 '10 at 06:29
What is the difference between Left Join and Normal fetching data from two tables? – tisuchi Sep 26 '15 at 16:18
@tisuchi As stated in my answer, the choice between outer join (`LEFT` / `RIGHT`) and "normal" (`JOIN` or multiple tables) relates to the desire for strictly joining rows vs. `NULL` substitutes on no-match. I suggest you take a look at the top answers for [Difference between INNER and OUTER joins](http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins) – jensgram Sep 28 '15 at 06:57

score 1 · Answer 2 · answered Apr 13 '10 at 05:55

1

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

answered Apr 13 '10 at 05:55

Alex

3,644
2
19
27

score 1 · Answer 3 · answered Apr 13 '10 at 05:55

1

You need to use a JOIN:

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

answered Apr 13 '10 at 05:55

Michael Mrozek

169,610
28
168
175

score 0 · Answer 4 · answered Apr 13 '10 at 06:04

0

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

answered Apr 13 '10 at 06:04

Phani Kumar PV

906
11
22

Fetch data from multiple MySQL tables

4 Answers4