Select the same column from multiple tables only somewhere = something

Question

I have two tables with very similar structures.

Universidades nombre | contenido | becas | fotos etc etc etc Internados nombre | todo | becas | fotos etc etc etc 

I want to write an SQL statement that selects nombre from both of them and returns it as an array only when it matches. From what I saw, UNION SELECT seems to be a way to do this. I added WHERE at the end, and I think it happens where it goes wrong. So far, I get the first row of the first table.

What am I typing wrong?

 $db = new PDO(DB_DSN, DB_USERNAME, DB_PASSWORD); $data = $db->prepare("SELECT nombre FROM internados UNION SELECT nombre FROM universidades WHERE nombre = ?"); $data->execute(array($nombre)); 

sorry, I want to get one result from these two tables. The names in the nombre column are individual and different in both tables.

Answer 1

One problem to point out before solving the problem is that each query in UNION is different and requires its own WHERE . The only condition that applies to UNION as a whole is ORDER BY . So your request requires some tweaking:

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

Secondly, if you want both tables to have the same nombre (which is not entirely clear, but I assume it is correct), then this will not work, because it just returns a single nombre if a value is found in both tables. Probably the best way to solve this is to simply make a connection:

 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 are looking for, so please let me know if I missed the mark.

You can think of JOIN and UNION as follows:

• JOIN : joins lines horizontally
  • Meets them on terms
  • Creates new columns
  • It doesn’t exactly create rows, because all the data comes from existing rows, but it will duplicate a row from one input, when the conditions correspond to several lines at another input. If both inputs have duplicates, then it multiplies the number of lines from one input by the number of matching lines with the other.
  • If there is no matching condition at all (think CROSS JOIN ), you can get a Cartesian product, which is each line in one input, consistent with each line in another.
  • When using OUTER join - LEFT , RIGHT , FULL - if the rows from the internal input (or input with FULL ) do not match, NULL will be placed in columns for another input.
• UNION : line stacks vertically
  • Usually creates new lines
  • No conditions are used, no real compliance
  • UNION by itself (not UNION ALL ) will delete duplicate lines, even if one input has no lines

Please note that the UNION parameter can be changed to complete the task, although 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 provide two values. Note that it is assumed that each table cannot have two identical names. If so, more work will be needed to make UNION way of doing the job.

Answer 2

most of the time a union can be done by doing the connection:

 SELECT nombre FROM internados UNION SELECT nombre FROM universidades WHERE nombre = ? 

it is better:

 SELECT nombre FROM internados i JOIN universidades u ON i.nombre = u.nombre AND nombre = ? 

which is easier to read. (you can also use JOIN syntax, but I prefer plain old manual joins).

But whether it is union or JOIN , always remember that both tables are combined into the result:

 nombre | todo | becas | fotos | ... | contenido | becas | ... 

so basically when you set the condition to nombre, you will either get an empty set, or the name you provided in the request. But as you wrote your union, only the second set gets the where clause, not the first. You must put the condition in both join queries:

 SELECT nombre FROM internados WHERE nombre = ? UNION SELECT nombre FROM universidades WHERE nombre = ?