0

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?

Improve this question
3
  • 1
    You could try joining the two tables together, and then executing a single query to get your result. Commented Jun 19, 2016 at 10:00
  • try something 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 = ? Commented Jun 19, 2016 at 10:13
  • @gdros Please post it as an answer with a bit of explanation. Commented Jun 19, 2016 at 10:14

3 Answers 3

Reset to default
1

The query using join

SELECT t2.* 
FROM table_1 t1
JOIN table_2 t2 ON t1.table_2_id = t2.id
WHERE t2.t1reg_no = ?
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Can you explain it a bit?
You can read about joins at dev.mysql.com/doc/refman/5.7/en/join.html . What exactly explanation is needed?
He wants the data from table_2 not from table_1... @Serg
0

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.

Improve this answer

2 Comments

Just a query: Wouldn't INNER JOIN be better for my use case?
Well in the exact case yes but generally it depends on what you want to do. The INNER JOIN will return to you no rows when there is no relevant row in table_2 and LEFT JOIN will return rows with null columns for every row in table_1 that have the specified reg_no
0

Try 

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

Example

table_1

id   reg_no   table_2_id
1     001        1
2     002        2
3     003        4 
4     004        7
5     005        8

table_2

id  column_test
1      A
2      B
3      C
4      D
5      E
6      F
7      G

Query

SELECT T2.* FROM table_2 T2 
LEFT JOIN table_1 T1 ON T1.table_2_id = T2.id 
WHERE T1.reg_no = 003;

Output

id  column_test
4     D
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.