CODEWITHSUNDEEP
mysqlsql
user2851669
user2851669

Reputation:

select multiple columns from nested subquery

I have the following tables - 

Table 1
id    name   hours

Table 2
id    department   project

Following is the query that I am running - 

SELECT id, name, department, TOTAL 
FROM table1 
WHERE hours='15' AND (id, department,TOTAL) IN 
    (SELECT id, department, count(*) AS TOTAL FROM table2 
     WHERE project is 'CS' and deparment IN ('cs', 'ece') 
     GROUP BY id, department HAVING count(*) > 1)

While running this query I am getting the following error - 

ERROR 1054 (42S22): Unknown column 'department' in 'field list''

Here is the link I followed to write this query.

What am I doing wrong here?

EDIT

  1. My aim is to get the deparments (cs and ece only) along with employee ids from table2 where the number of employees working in project cs is more than 1.
  2. Then for the ids that I got from table2, I want to get the name and working hours from table1.
  3. Initially, I was writing a for loop for the second part but writing a for loop increases the number of queries made and hence increases load on the server. So, I want to do it in one shot using nested queries.

Note - I would prefer not using join due to the time complexity of join operation.

Example

table1 
id    name    hours
1     a       15
2     b       16
3     c       15

table2
id    department   project
1       cs          cs
2       ece         cs
3       cs          cs
4       mech        cs

Expected ouput - 
id     name    department   hours
1      a       cs           15
2      c       cs           15

Upvotes: 0

Views: 1126

Answers (1)

Mr. Engineer
Mr. Engineer

Reputation: 3515

You are selecting department from table 1. And i cant see any field name with department in your table 1.

Generally Unknown column in 'field list' appears when you are trying to select field which are not present in database.

Upvotes: 1

Related Questions