Mysql: Get results from all tables

Question

Hello Guys,

I just wanted to ask is there an easier way to get records from all tables of db using a single query where some tables are linked using PKs and FKs.

For example, i have a drop down which has 'Cars', 'Bikes', 'Trucks', etc

When I select Cars, i get results only from the Cars table.

But now i have added 'All' to that drop down meaning getting results from all tables for the search query.

How do i do it?

Answer 1

If one table contains a foreign key that references a second table, you can use a join. Please post the DDL of your tables and describe the result you'd like to achive in more detail.

Answer 2

One possibility is to alter the database structure so that the three tables Cars, Bikes, and Trucks are combined into one table—vehicles—with an added column which indicates which type of vehicle it is.

Another possibility is to create a view which does the same thing. I'd suggest specifics, but I need to see the table and key definitions.

Answer 3

You could use a UNION:

SELECT * FROM cars UNION
SELECT * FROM bikes UNION
SELECT * FROM trucks;