CODEWITHSUNDEEP
mysqlsql
Alex
Alex

Reputation: 68406

MySQL - Select specific rows from multiple tables

I have a set of database tables named like:

site_1_details
site_2_details
site_3_details
...
site_420_details

all tables have the same fields, like:

ID  | SETTING | VALUE 
----------------------
1     name      Site 1 Name
2     desc      Site 1 Desc
3     email     Site 1 Email...
...

(only the value fields are different)

How can I get a set of values from certain tables?

For example, I want to get the name & email values from sites 3,7 and 15. How could I do that with a SQL query?

Upvotes: 1

Views: 2164

Answers (1)

Marc B
Marc B

Reputation: 360592

SELECT 3 AS siteID, name, email
FROM site_3_details

UNION

SELECT 7 AS siteID, name, email
FROM site_7_details

UNION

SELECT 15 AS siteID, name,email
FROM site_15_details

This is a horribly bad design. Why couldn't you put a "siteID" field into a single table, which'd reduce the query to:

SELECT name, email
FROM site_details
WHERE siteID IN (3,7,15);

comment followup:

Ah well, then you just modify the individual queries:

SELECT 7 AS siteID, ID as fieldID, name AS fieldName
FROM site_7_details WHERE SETTING IN ('name', 'email')

UNION

....

Any reason you've designed the tables like this? Sounds like you're trying to implement your own database on TOP of a database engine which is already perfectly suited to doing this kind of relational data handling.

Upvotes: 4

Related Questions