CODEWITHSUNDEEP
phpmysql
fredmarks
fredmarks

Reputation: 327

MySQL multiple Select?

I am running a query on an employees database. I want to return rows where employees wages exceed amount $x

SELECT 
    employees_table.employee_id, 
    employees_table.employee_name,
    (SELECT SUM(wages_table.wages)
     FROM wages_table
     WHERE wages_table.employee_id = employees_table.employee_id) AS wages
WHERE wages >= 100"

It's returning "unknown column wages"

Upvotes: 0

Views: 86

Answers (6)

sashkello
sashkello

Reputation: 17871

You can't use an alias wages >= 100 in WHERE clause.

I believe you are simply trying to make a JOIN and GROUP BY query. You can then filter by wages using HAVING for which an alias is allowed:

SELECT 
    e.employee_id, 
    e.employee_name,
    SUM(w.wages) AS wages
FROM wages_table w
JOIN employee_table e ON w.employee_id = e.employee_id
GROUP BY e.employee_id
HAVING wages >= 100

Upvotes: 1

Filipe Silva
Filipe Silva

Reputation: 21657

I think you want:

SELECT employees_table.employee_id,
  employees_table.employee_name,
  SUM(wages_table.wages) AS wages
FROM employees_tablee
INNER JOIN wages_table ON wages_table.employee_id = employees_table.employee_id
GROUP BY 1, 2
HAVING SUM(wages_table.wages) >= 100

Upvotes: 2

Jason Heo
Jason Heo

Reputation: 10236

You need to explain relationship between wages_table and employees_table.

If one to one relationship :

SELECT employees_table.employee_id, employees_table.employee_name, wages_table.wages
FROM wages_table, employees_table
WHERE wages_table.employee_id = employees_table.employee_id
AND wages >= 100

If one to many relationship, GROUP BY and SUM(wages_table.wages) required as other people said.

Upvotes: 0

Alexander
Alexander

Reputation: 809

Try rewrite to:

select employees_table.employee_id, employees_table.employee_name, SUM(wages_table.wages)
from employees_table,
     wages_table 
where wages_table.employee_id = employees_table.employee_id
group by employees_table.employee_id, employees_table.employee_name
having SUM(wages_table.wages) >= 100

Upvotes: 0

Michaël Benjamin Saerens
Michaël Benjamin Saerens

Reputation: 875

The steps to process a query are these

1) From statement

2) Where

3) Select

4) Order By

As you can see, the Where is executed before the Select. Therefore: all aliases defined in the select are not yet available in your Where statement. A workaround is using an inner select in your From clause that gives you the the employee_id and the sum of its wages. Then you can join them later on and your alias is usable.

Upvotes: 0

hangman
hangman

Reputation: 875

try this

select * from (
SELECT employees_table.employee_id, employees_table.employee_name,
        (SELECT SUM(wages_table.wages)
           FROM wages_table
       WHERE wages_table.employee_id = employees_table.employee_id) AS wages
       )tmp where wages>=100

Upvotes: 0

Related Questions