Select from multiple tables on reference table with same column name MYSQL/PHP

Question

I need some help with MySQL/PHP. Which is the faster execution in code point of view.

I have below tables

1. table_content
--------------------------------
id      | section   | content_id
--------------------------------
1       | A         | 15
2       | B         | 25
3       | A         | 9
--------------------------------


2. table_a
--------------------------------
id      | name      | message
--------------------------------
9       | John      | Hello Everyone
15      | Smita     | Hi
17      | Vinayak   | How are you?
--------------------------------


3. table_b
--------------------------------
id      | label     | description
--------------------------------
1       | David     | D1
5       | Alia      | D2
25      | Vinay     | D3
--------------------------------

I have above table structure. For me table_content is main table. I want below output through MySQL/PHP [As array and section as key].

Output
------------------------------------------------
id      | section   | name      | message
------------------------------------------------
1       | A         | Smita     | Hi
2       | B         | Vinay     | D3
3       | A         | John      | Hello Everyone
------------------------------------------------

I have tried with SWITCH case. But not getting exact output.

Which is the better performance and fast execution? with MySQL or PHP. I have thousands of data like this.

Please help me to solve this problem.

Does this answer your question? [Joining three tables using MySQL](https://stackoverflow.com/questions/3709560/joining-three-tables-using-mysql) — nbk, Nov 15 '21 at 15:01

score 0 · Answer 1 · answered Nov 15 '21 at 14:49

0

Should be acheved via a JOIN.

Also ensure you have configured appropriater indexing, otherwise it will perform badly when you get to a large volume of data.

answered Nov 15 '21 at 14:49

Brian

8,418
2
25
32

1

this is a comment not an answer – nbk Nov 15 '21 at 14:57
Yes it is, I have told him what he needs to do. No point spoon feeding people or they will not learn. I have also highlighted potential pitfalls for them to investigate for further learing to ensuring whatever he is building will handle any potential lareg volume. – Brian Nov 15 '21 at 15:15

score 0 · Answer 2 · answered Nov 15 '21 at 17:31

As i posted in my comment you need to Join the both tables, which must be UNION

SELECT tc.`id`, tc. `section` ,t1.`message`
FROM table_content tc JOIN (SELECT `id`, `name`, `message` FROM table_a UNION SELECT `id`, `label`, `description` FROM table_b) t1 
ON tc.`content_id` = t1.`id`

id | section | message       
-: | :------ | :-------------
 1 | A       | Hi            
 2 | B       | D3            
 3 | A       | Hello Everyone

db<>fiddle here

Select from multiple tables on reference table with same column name MYSQL/PHP

2 Answers2