MySql get data from multiple tables results from a select

Question

I have a table "move" like this

----------------------------------
| id | ... | ... |  tab  | idTab |
----------------------------------
| 1  | ... | ... | bike  |  14   |
| 2  | ... | ... | car   |  57   |
| 3  | ... | ... | car   |  23   |
| 4  | ... | ... | bike  |  43   |
| 5  | ... | ... | boat  |  20   |
| .. | ... | ... | ...   | ....  |
----------------------------------

tab "bike"

---------------------------------
| id | code | name  | matr | ... |
---------------------------------
| .. | ...  | ....  | .... | ... |
| 14 | AAA  | MARIO | 111  | ... |
| .. | ...  | ....  | .... | ... |
| 43 | A1C  | JOHN  | EEE  | ... |
| .. | ...  | ....  | .... | ... |
---------------------------------

tab "car"

---------------------------------
| id | code | name  | matr | ... |
---------------------------------
| .. | ...  | ....  | .... | ... |
| 23 | 123  | JACK  | WER  | ... |
| .. | ...  | ....  | .... | ... |
| 57 | 2A2  | FRANK | MSS  | ... |
| .. | ...  | ....  | .... | ... |
---------------------------------

tab "boat"

---------------------------------
| id | code | name  | matr | ... |
---------------------------------
| .. | ...  | ....  | .... | ... |
| 20 | UJN  | PETER | WSX  | ... |
| .. | ...  | ....  | .... | ... |
---------------------------------

I would like to have in a single query all datas of "move" and the datas of each tables present in "move" with specific idTab.

The result will be like this

--------------------------------------------------------------
| id | ... | ... |  tab  | idTab | code | name  | matr | ... |
--------------------------------------------------------------
| 1  | ... | ... | bike  |  14   | AAA  | MARIO | 111  | ... |
| 2  | ... | ... | car   |  57   | 2A2  | FRANK | MSS  | ... |
| 3  | ... | ... | car   |  23   | 123  | JACK  | WER  | ... |
| 4  | ... | ... | bike  |  43   | A1C  | JOHN  | EEE  | ... |
| 5  | ... | ... | boat  |  20   | UJN  | PETER | WSX  | ... |
| .. | ... | ... | ...   | ....  | ...  | ....  | .... | ... |
--------------------------------------------------------------

I don't know in effect if I have or not a specific table in move. I can make a query like this

SELECT * FROM move WHERE id>0

and then a foreach with

SELECT * FROM move["tab"] WHERE id=move["idTab"]

but is very hard work because table move is very long...

I try with this but (obviously) don't work...

SELECT M.*, T.* FROM move as M, M.tab as T WHERE M.id>0 AND M.idTab=T.id

@Strawberry Because then SO users would not have any work to do, and we obviously don't want that :-) — Tim Biegeleisen
– Tim Biegeleisen, Commented May 22, 2017 at 7:21

Tim Biegeleisen · Accepted Answer · 2017-05-22 07:24:43Z

1

Your database design is not ideal, and I don't think you should be having separate tables for each type of vehicle. That being said, if you bring the three tables into a single one using a UNION, then a single join could yield your desired result.

SELECT t1.*, t2.*
FROM move t1
INNER JOIN
(
    SELECT id, code, name, matr, 'bike' AS tab
    FROM bike
    UNION ALL
    SELECT id, code, name, matr, 'car'
    FROM car
    SELECT id, code, name, matr, 'boat'
    FROM boat
) t2
    ON t1.idTab = t2.id AND
       t1.tab   = t2.tab

I added a computed column for the type of table under the assumption that perhaps the same id could appear in more than one vehicle table.

answered May 22, 2017 at 7:24

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Francesco G. Over a year ago

Thanks... I have more table that contains more informations of vehicles and a table like join of these with general informations.

Tim Biegeleisen Over a year ago

@FrancescoG. ...and your question is?

Francesco G. Over a year ago

it's only to explain because I have designed like this my dbase. just wondering, if (for example) I will haven't in "move" an item "boat", the query will work?

Strawberry · Accepted Answer · 2017-05-22 07:23:12Z

0

Select 'bike' vehicle,column1,.... from bike
Union
Select 'boat',column1,... from boat
Union...

answered May 22, 2017 at 7:23

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Collectives™ on Stack Overflow

MySql get data from multiple tables results from a select

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related