CODEWITHSUNDEEP
mysqlselect
wpmika
wpmika

Reputation: 21

MySQL select from multiple tables that have different columns number

I'm new to MySQL so I really need some help with an issue I'm facing: I have 7 tables in same database with some datas from tests: The 7 tables have different columns but they all have these columns:

  • name.
  • second_name.
  • status.

In status are added current status of each student (accepted or rejected) and I want to display using select the name, second_name from the 7 tables where status = accepted.

I managed to display from 1 table

SELECT name, second_name FROM test1 WHERE status="accepted";

But I can not figure out how to display from all 7. It will be a real help for me if somebody could give me a hint.

Upvotes: 1

Views: 465

Answers (2)

Andrei Beliak
Andrei Beliak

Reputation: 686

IMHO it's better to normalize database to have all the names, secondnames and statuses in the separate table and do the only select instead of UNION to improve performace.

Upvotes: 2

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726559

If you do not mind duplicate student names with multiple accepted tests, you can try doing it with UNION ALL:

(SELECT name, second_name FROM test1 WHERE status='accepted')
UNION ALL
(SELECT name, second_name FROM test2 WHERE status='accepted')
UNION ALL
(SELECT name, second_name FROM test3 WHERE status='accepted')
-- ...and so on

Upvotes: 2

Related Questions