0

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
  • How are `table 1` and `table 2` linked together? – Ivan Starostin Feb 16 '16 at 17:27
  • They are not i.e. no foreign key relationship. –  Feb 16 '16 at 17:42
  • Your request is about two tables: something with hours and something with dep and proj. What is the point of the query you are attempting to build? Note that both tables are mentioned in your example and you are saying they are not linked. – Ivan Starostin Feb 16 '16 at 17:50
  • I made edits to the question. I hope it makes it clearer. –  Feb 16 '16 at 18:02
  • There's no department field in table1 for you to select. – Dave Feb 16 '16 at 18:06
  • Could you describe item 2? If tables are not linked _how_ should _working hours_ be linked to the employees in the query? Do you mean `cross join` logic with no join predicate? – Ivan Starostin Feb 16 '16 at 18:09
  • You'd better provide an example of desired resultset – Ivan Starostin Feb 16 '16 at 18:11
  • Re: "I would prefer not using join due to the time complexity of join operation": Have you actually tested its performance? You shouldn't assume that how you'd naively implement join vs. a subquery has anything to do with how your DBMS actually does. – ruakh Feb 16 '16 at 18:15
  • Question edited with an example –  Feb 16 '16 at 18:17
  • @ruakh You correctly pointed it out. Turns out it was a naive assumption to make and in many cases joins perform better than sub query (http://stackoverflow.com/questions/3856164/sql-joins-vs-sql-subqueries-performance) –  Feb 16 '16 at 18:30
  • An sqlfiddle would be nice (along with a desired result) – Strawberry Feb 16 '16 at 18:30

1 Answers1

1

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.

Mr. Engineer
  • 3,522
  • 4
  • 17
  • 34