Abdul Fatir Abdul Fatir - 5 months ago 16
MySQL Question

MySQLi 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

The query using join

SELECT t1.* 
FROM table_1 t1
JOIN table_2 t2 ON t1.table_2_id = t2.id
WHERE t2.t1reg_no = ?