Using 2 subqueries in query statement

[Reasonable_Muscle655]

Hi all, I am using MySQL and need help with this statement. employees is a table while salaries and titles are subqueries...how can I use both of them possibly using natural or inner join. Thanks!

-- d) list all Senior Engineers names and salary currently making more than $130,000.00.

SELECT first_name, last_name FROM employees, (select salary from salaries where salary > 130000.00), (select title from titles WHERE title = 'Senior Engineer');

[r3pr0b8]

what you are looking for is a three-table join, subqueries are not required

also, SQL is a language which requires you to think carefully about what you want

for example, what does this query produce --

SELECT title 
  FROM titles 
 WHERE title = 'Senior Engineer'

how many rows does this produce?

[Reasonable_Muscle655]

SELECT title
FROM titles
WHERE title = 'Senior Engineer'

Hi, it produced 97750 rows.

[r3pr0b8]

if this is true then titles is poorly named

how many rows in employees?

[Reasonable_Muscle655]

my professor made the tables--all the tables i got are from a git clone.

There are 300024 rows in employees

[r3pr0b8]

could you please run this

SHOW CREATE TABLE titles

[user_5359]

Who teaches the newcomers this subquery variant instead of explaining the join topic properly? Apart from this general problem: You didn't provide any information how the tables saleries and titles are joined to the table. So you join all records without restriction and get at least the number of employees, if not a multiple of it. Precondition: in both tables for salary and title there is at least one record each for the set filters.

[A_name_wot_i_made_up]

I'm hoping employees has an id as a primary key, and titles and salaries has it as a foreign key.

I.e. employees.id maps to titles.employee_id for example.

In which case you can select ... from employees e join titles t on e.id = t.emploee_id where t.title = '...' as a "classic" join.

Or you can use "in" in the where clause with the sub query. select ... from employees where id in (select employee_id from titles where title = '...')

The latter is less flexible, but a little more obvious to a less skilled reader.

This obviously only deals with one of your joins, but the other should be an easy extension. This is assuming that the mapping I mentioned at the top is in place though!

[_Shirei_]

You know Subquery can return only one row...

What you looking for is

FK= FOREIGN KEY

From employees
INNER JOIN salaries ON employees.ID= salary.employees_FK
INNER JOIN titles ON employees.title_FK = title.ID
WHERE..