MySQL - Select from multiple tables and display multiple fields-CodePudding

I have a WordPress multisite with 100 *_options tables namely WPM_101_options to WPM_201_options.

I am trying to query option names admin_email, siteurl and blogname.

This code will work on 1 table:

SELECT option_name, option_value 
FROM wpm_104_options
WHERE option_name = 'admin_email' 
UNION
SELECT option_name, option_value 
FROM wpm_104_options
WHERE option_name = 'siteurl'
UNION
SELECT option_name, option_value 
FROM wpm_104_options
WHERE option_name = 'blogname'

But how do I get it working with multiple tables? I tried 2 tables with...

SELECT option_name, option_value 
FROM wpm_104_options,wpm_105_options
WHERE option_name = 'admin_email' 
UNION
SELECT option_name, option_value 
FROM wpm_104_options,wpm_105_options
WHERE option_name = 'siteurl'
UNION
SELECT option_name, option_value 
FROM wpm_104_options,wpm_105_options
WHERE option_name = 'blogname'

and

SELECT option_name, option_value 
FROM wpm_104_options,wpm_101_options
WHERE option_name = 'admin_email' 
GROUP BY option_name
UNION
SELECT option_name, option_value 
FROM wpm_104_options,wpm_101_options
WHERE option_name = 'siteurl'
GROUP BY option_name
UNION
SELECT option_name, option_value 
FROM wpm_104_options,wpm_101_options
WHERE option_name = 'blogname'
GROUP BY option_name

and

SELECT wpm_104_options.option_name as n104, wpm_104_options.option_value as v104, wpm_101_options.option_name as n101, wpm_101_options.option_value as v101
FROM wpm_104_options, wpm_101_options
WHERE option_name = 'admin_email'

...but I get an error:

Error Code: 1052. Column 'option_name' in field list is ambiguous 0.000 sec

CodePudding user response：

The error says that you have tried to query two tables that each have an option_name column and the DB has no way of knowing which of the two you mean.

What you are doing by specifying two tables in the FROM clause is an implicit join. If you want columns from two tables joined in your result, you would need to specify which columns you mean by prefixing them with the table name, e.g.

SELECT wpm_104_options.option_name, wpm_104_options.option_value, wpm_101_options.option_name, wpm_101_options.option_value 
FROM wpm_104_options,wpm_101_options
WHERE wpm_104_options.option_name = 'admin_email' 
GROUP BY wpm_104_options.option_name

However, this usually only makes sense if you also specify in which way the columns are supposed to be joined, i.e. define their relationship in the WHERE clause. Otherwise you'd just get every possible combination of rows from the two tables.

I guess what you really want is something else though. I believe you simply want the combined results of the query you showed for one table, but from two tables. This you don't achieve with an implicit join with two tables in the FROM clause but instead by using UNION or UNION ALL like you already did. E.g.

SELECT option_name, option_value 
FROM wpm_104_options
WHERE option_name = 'admin_email' 
UNION
SELECT option_name, option_value 
FROM wpm_104_options
WHERE option_name = 'siteurl'
UNION
SELECT option_name, option_value 
FROM wpm_104_options
WHERE option_name = 'blogname'
UNION 
SELECT option_name, option_value 
FROM wpm_101_options
WHERE option_name = 'admin_email' 
UNION
SELECT option_name, option_value 
FROM wpm_101_options
WHERE option_name = 'siteurl'
UNION
SELECT option_name, option_value 
FROM wpm_101_options
WHERE option_name = 'blogname'

You can also greatly shorten this by just specifying the different conditions in the WHERE clause with OR:

SELECT option_name, option_value 
FROM wpm_104_options
WHERE option_name = 'admin_email' 
   OR option_name = 'siteurl'
   OR option_name = 'blogname'
UNION 
SELECT option_name, option_value 
FROM wpm_101_options
WHERE option_name = 'admin_email' 
   OR option_name = 'siteurl'
   OR option_name = 'blogname'

or even

SELECT option_name, option_value 
FROM wpm_104_options
WHERE option_name IN ('admin_email', 'siteurl', 'blogname')
UNION 
SELECT option_name, option_value 
FROM wpm_101_options
WHERE option_name IN ('admin_email', 'siteurl', 'blogname')