Abdul Fatir Abdul Fatir - 5 months ago 8
MySQL Question

MySQL query from multiple tables?

I have two tables say named

table_1
and
table_2
. The schema is somewhat as follows.

table_1

+----+--------+-----+------------+
| id | reg_no | ... | table_2_id |
+----+--------+-----+------------+


table_2

+----+-----+
| id | ... |
+----+-----+


The column
table_2_id
in
table_1
refers to the column
id
in
table_2
. Now, I have to get the
table_2_id
for a specific
reg_no
and then use that
table_2_id
to get data from
table_2
.

I currently do it as follows and it works.

$stmt = $this->conn->prepare("SELECT table_2_id from table_1 WHERE reg_no = ?");
$stmt->bind_param("s", $reg_no);
$stmt->execute();
$stmt->bind_result($table_2_id);
$stmt->fetch();
$stmt->close();
$stmt = $this->conn->prepare("SELECT * from table_1 WHERE id = ?");
$stmt->bind_param("i", $table_2_id);
$stmt->execute();
...


Is this the correct way to do it? Is there some other more efficient query to perform this task?

Answer

It should be more efficient to do this with one query like this:

SELECT table_2.* 
FROM table_1 LEFT JOIN table_2 ON table_1.table_2_id=table_2.id 
WHERE table_1.reg_no = ?

table_1 LEFT JOIN table_2 ON table_1.table_2_id=table_2.id will join table_1 and table_2. More specifically it will create a table which will have all columns from both table_1 and table_2 and then it will put in it all rows from table_1. Also when there is a row in table_2 with id same as the table_2_id it will also fill the tables_2's columns.

The above action will be limited to only those rows where table_1.reg_no = ?

Finally with SELECT table_2.* we get only the columns from the table_2 as in your example.

Comments