How to get result by joining two queries in PHP

Question

I am using two queries for getting my result, but I want to make them one query and execute it to get result. Here is the scenario:

Query 1:

select id from tableA where reference_id = 2

and by getting "Id" I need to execute another query

Query 2:

Select * from tableB where id = "(Id coming from query one)"

Is it possible to make them one query ??? If Yes then kindly guide me. Thanks in advance.

your not using the `id` in tableB as the foreign key for tableA are you? Because that would mean that you are missing a Primary key. And if you are not, then you have your tables backwards, at least from a logic stand point. Because one record in tableB can have many records in tableA. Typically that is a subordinate table. — ArtisticPhoenix, Apr 06 '18 at 06:41

Karlo Kokkak · Answer 1 · 2018-04-06T06:14:32.597

2

Alternative using INNER JOIN.

SELECT tableA.id, tableB.column1, tableB.column2 FROM tableA
INNER JOIN tableB ON tableA.id = tableB.id
WHERE
tableA.reference_id = 2

edited Apr 06 '18 at 06:14

answered Apr 06 '18 at 06:07

Karlo Kokkak

3,674
4
18
33

score 2 · Answer 2 · answered Apr 06 '18 at 06:27

You can make it by using join or subquery

Using Subquery

Select * from tableB where id in (select id from tableA where reference_id = 2)

Using Join

Select table1.id, table1.column_name2, table2.ref_id, table2.column_name2 from table1 leftjoin table2 on table1.id=table1.ref_id where table2.column_name2=2

ArtisticPhoenix · Accepted Answer · 2018-04-06T06:35:39.150

Yep like this

SELECT
   a.id,
   b.*
FROM
   tableB AS b
JOIN
   tableA as a ON a.reference_id = b.id
WHERE
   b.id = :id

The :id is a named place holder for PDO which I highly recommend using.

$stmt = $PDO->prepare($sql);
$stmt->execute([':id'=>2]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

Now at first glance you might think this is like @Karlo Kokkak answer. Well I'm using Join not Inner Join. Just kidding, I know they are the same thing. I just wanted a better way to say it then "they are the same thing".

The real difference is that I actually used the reference_id as the foreign key field. And I arranged the tables with the One relation first and then the Many relation second.

tableB has a One to Many relationship with tableA, because tableA has the FK in it. Therefor you can have many records in tableA that refer to tableB. Assuming reference_id is the FK, but I don't see what else it could be. I would consider it a horrible design if the id in one table was the Primary key, and then the id in the other table was the FK.

Anyway, My table arrangement is backwards to that one, but IMO the correct way (in most cases). This is probably an oversite by the OP.

Last thing is I used a prepared statement in mine.

Cheers.

You know many answers work for me :) I am confused which answer should I mark correct. I think you probably did much effort by writing a detailed answer so you gain my vote. Thanks Man :) — Haris Khan, Apr 06 '18 at 06:44
Not really, took me like 10 seconds. I type fast. I live breath and eat Database models, yum... SQL... — ArtisticPhoenix, Apr 06 '18 at 06:46

score 0 · Answer 4 · answered Apr 06 '18 at 06:03

0

Simple. use this code.

SELECT * FROM tableA, tableB WHERE tableA.reference_id = 2 AND tableB.id=tableA.id;

answered Apr 06 '18 at 06:03

Lahiru Madusanka

270
2
13

2

Please don't use implicit joins: https://stackoverflow.com/a/5654338/307138 – Ocaso Protal Apr 06 '18 at 06:09

score 0 · Answer 5 · answered Apr 06 '18 at 06:04

0

Yes, you need to use the LEFT JOIN.

SELECT
    a.id,
    b.column_name as column_from_tableA,
    b.id,
    b.column_name as column_from_tableB
FROM tableA a
LEFT JOIN tableB b
    ON b.id = a.id
WHERE a.reference_id=2;

answered Apr 06 '18 at 06:04

Marvin WordWeaver Parsons

610
1
7
14

score 0 · Answer 6 · answered Apr 06 '18 at 06:10

with subquery ;

SELECT column-names
  FROM table-name1
 WHERE value IN (SELECT column-name
                   FROM table-name2 
                  WHERE condition)

So your query is =>

Select * from tableB where id IN (select id from tableA where reference_id = 2)

How to get result by joining two queries in PHP

6 Answers6