How to add a second SELECT based on the results of the first?-CodePudding

I have a complex SELECT query with joins, but to simplify it looks like this:

SELECT name, surname FROM table1;

The resulting output is:

name     surname
john     smith
jacob    smath
judy     smooth

I also have another complex query, let's say it selects a salary and it uses name and surname as where parameters:

SELECT salary FROM table2 where name = "John" and surname = "Smith"

It only returns a single value - salary for each name and surname combination.

I want to somehow combine these queries so it joins the second select to the first select, like this:

name     surname   salary
john     smith     100
jacob    smath     50
judy     smooth    80

I've tried something like (pseudocode):

SELECT name, surname FROM table1
as data
full outer join(
   SELECT salary FROM table2 where name = data.name and surname = data.surname
)

But it says:

There is an entry for table "data" but it cannot be referenced from this part of the query.

How do I do this?

CodePudding user response：

You can use an IN clause in order to select the data from table2 based on the table1 query result:

SELECT *
FROM table2
WHERE (name, surname) IN (SELECT name, surname FROM table1);

Or join to get the combined results:

SELECT *
FROM table2 t2
JOIN (SELECT name, surname FROM table1) t1 USING (name, surname);

CodePudding user response：

No need for a derived table (sub-query), you can join to the table directly.

SELECT data.name, data.surname, table2.salary
FROM table1 as data
  full outer join table2 
    ON table2.name = data.name 
   and table2.surname = data.surname
   and table2.name = 'John'
   and table2.surname = 'Smith'

If you move the condition on name and surname to the WHERE clause that would turn the outer join back into an inner join.

This answers your question about the syntax error, but I think the IN condition from Thorsten's answer is what you are really looking for.

CodePudding user response：

try

SELECT t1.name, t1.surname, t2.salary
FROM table1 t1
INNER JOIN table2 t2 ON t1.name = t2.name and t1.surname=t2.surname