CODEWITHSUNDEEP
mysqldatabasejdbc
kholofelo Maloma
kholofelo Maloma

Reputation: 1003

nesting select statement in MySQL

I am new to relational Databases... I want to list all data in many tables within the same DB.

I have a table that contain NAMES of other tables. Like this:

mysql> select * from CarKeys;


+-------+
| Name  |
+-------+
| Audi  |
| Honda |
+-------+


mysql> select * from Audi;

+-------+---------+--------+
| Model | NumDoor | Colour |
+-------+---------+--------+
| A4    | 4       | White  |
+-------+---------+--------+


mysql> select * from Honda;
+------------+---------+--------+
| Model      | NumDoor | Colour |
+------------+---------+--------+
| Civic      | 4       | White  |
| Civic      | 4       | White  |
| HomdaModel | 5       | Red    |
+------------+---------+--------+

But now, I don't wanna be calling select * from Honda and select * from Audi seperately. I want a single command that will select * from all tables Named within CarKeys table

I tried this:
SELECT * FROM (SELECT * FROM CarKeys);

but I got a this error message:
ERROR 1248 (42000): Every derived table must have its own alias

how can I get all info for all tables within this Database? The name of the Schema I am using is WebApp

Upvotes: 0

Views: 79

Answers (3)

mehdi
mehdi

Reputation: 1755

this tables are haven't good relation, you can try:

SELECT * FROM Audi
UNION
SELECT * FROM Honda

Result:

+------------+---------+--------+
| Model      | NumDoor | Colour |
+------------+---------+--------+
| A4         | 4       | White  |
| Civic      | 4       | White  |
| Civic      | 4       | White  |
| HomdaModel | 5       | Red    |
+------------+---------+--------+

Upvotes: 1

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

You have to used a Stored Procedure.

Use a loop, over Carkeys.Name stored it in variable (v1)

Now execute using Prepared Statement

select * from v1

Upvotes: 0

mimipc
mimipc

Reputation: 1374

There's a big problem in your SQL design. You souldn't call tables this way. Why wouldn't you create a single table for all your cars and add a column refering to the table containing the names ?

+-------+-------+
| ID    | Name  |
+-------+-------+
| 1     | Audi  |
| 2     | Honda |
+-------+-------+



+------------+---------+--------+--------+
| Model      | NumDoor | Colour | Brand  |
+------------+---------+--------+--------+
| Civic      | 4       | White  | 2      |
| A4         | 4       | White  | 1      |
| Civic      | 4       | White  | 2      |
| HomdaModel | 5       | Red    | 2      |
+------------+---------+--------+--------+

Upvotes: 1

Related Questions