Select Same Column from Multiple Tables Only Where Something = Something

Select same column from multiple tables only WHERE something = something

One issue to point out before we solve the problem is that each query in a UNION is distinct and requires its own WHERE clause. The only clause that applies to the UNION as a whole is ORDER BY. So your query as is needs some tweaking:

SELECT nombre
FROM dbo.internados
WHERE nombre = ? -- the added line
UNION
SELECT nombre
FROM dbo.universidades
WHERE nombre = ?
;

Second, if you want the two tables to both have the same nombre (which is not completely clear but I'm guessing that's right), then that won't work because it simply returns one nombre if the value is found in either table. Probably the best way to solve this is to just do a join:

SELECT I.nombre
FROM
   dbo.internados I
   INNER JOIN dbo.universidades U
      ON I.nombre = U.nombre
WHERE
   I.nombre = ?
   AND U.nombre = ? -- perhaps not needed, but perhaps helpful
;

I am not 100% sure that I understand exactly what you're looking for, so please speak up if I've missed the mark.

You can think about JOIN and UNION this way:

• JOIN: connects rows horizontally
  
  • Matches them on conditions
  • Creates new columns
  • Doesn't exactly create rows because all data comes from existing rows, but it will duplicate a row from one input when the conditions match multiple rows in the other input. If both inputs have duplicates then it multiplies the count of rows from one input by the count of matching rows from the other.
  • If there is no match condition at all (think CROSS JOIN) then you can get a cartesian product which is each row in one input matched to each row in the other.
  • When using an OUTER join--LEFT, RIGHT, FULL--if rows from the inner input (or either input with FULL) do not match the other, NULLs will be placed into the columns for the other input.
• UNION: stacks rows vertically
  
  • Generally, creates new rows
  • No conditions are used, there is no real matching
  • UNION by itself (not UNION ALL) will remove duplicate rows, even if one input has no rows

Note that the UNION could be modified to do the job, though this is not ideal:

SELECT nombre
FROM (
   SELECT nombre
   FROM dbo.internados
   WHERE nombre = ?
   UNION ALL
   SELECT nombre
   FROM dbo.universidades
   WHERE nombre = ?
) N
GROUP BY nombre
HAVING Count(*) = 2
;

In this way we ensure there are two values. Note this assumes that there can't be two of the same name in each table. If that's true, more work would be needed to make the UNION method do the job.

Select same columns from multiple tables

using the union :

    select productid,price from table1
union select productid,price from table2
union select productid,price from table3

select from multiple tables with the same column

#standardSQL
SELECT a, b, c
FROM (
    SELECT a, b, c, d, e 
    FROM `project.hourly.*`
    WHERE _TABLE_SUFFIX BETWEEN '15' AND '16'
)

Above is assuming that hourly is your dataset

Select columns from multiple tables

SELECT T1.a, T1.b, ..., TN.z
FROM T1 INNER JOIN T2 ON T1.pk = T2.pk
INNER JOIN T3 on T1.pk = T3.pk
...
INNER JOIN TN on T1.pk = TN.pk
AND TN.pk = "abc"

Or in a shorter form (thanks @Abelisto!).

SELECT T1.a, T1.b, ..., TN.z
FROM T1 JOIN T2 using(pk)
JOIN T3 using(pk)
...
JOIN TN using(pk)
WHERE TN.pk = "abc"

Multiple tables joined to a table via single column

It all depends on what they mean and if you need to know the columns the values are from.

This would get all the columns and you would have NULL values from the non-matching B, C, D tables:

SELECT *
FROM   a1
       INNER JOIN a ON a1.aid = a.id
       LEFT OUTER JOIN b ON a.extid = b.extid
       LEFT OUTER JOIN c ON a.extid = c.extid
       LEFT OUTER JOIN d ON a.extid = d.extid

Or, this would get only the relevant values and give you the type they belong to in fewer columns:

SELECT *
FROM   a1
       INNER JOIN a ON a1.aid = a.id
       INNER JOIN (
         SELECT extid, 'B' AS type, pqr_col AS col1, qrs_col AS col2 FROM b
         UNION ALL
         SELECT extid, 'C', abc_col, bcd_col FROM c
         UNION ALL
         SELECT extid, 'D', xyz_col, yza_col FROM d
       ) bcd
       ON a.extid = bcd.extid

Select all data from multiple table ON same field value

A union query might make more sense here:

SELECT employee_job FROM regular_employee WHERE join_date = '2020/02'
UNION
SELECT employee_job FROM contract_employee WHERE join_date = '2020/02';

UNION by default will remove duplicate jobs which might appear in one/both of the two tables.

MySQL - Select from multiple tables and display multiple fields

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')

---

Related Topics