• Register
  • TOS
  • Privacy
  • @NeoGAF

unomas
Banned
(03-31-2012, 08:44 PM)
unomas's Avatar
I'm stuck on question 2 for a homework assignment, I've asked my professor who gave an example on how to do it and some classmates but still no luck. For some reason subqueries just don't register with me at all and we have to use a subquery to show this.. Any thoughts SQL GAF?



Use a subquery to display the employee identification number and employee name of all employees of the production department.


SELECT EMPLOYEE_ID, EMPLOYEE_NAME, DEPARTMENT_ID, DEPARTMENT_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_NAME IN (SELECT DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = ‘PRODUCTION’);

And I get the error "quoted string not properly terminated". My question is am I even somewhat close here?

The tables are EMPLOYEES and DEPARTMENTS. EMPLOYEES contains the employee_id, employee_name, while the DEPARTMENTS table contains department_id and department_name.
Tabris
flaming jackass
(03-31-2012, 08:47 PM)
Tabris's Avatar
EDIT - Nevermind. I read it more closely.

You need something to join the tables on. That's a horrible schema if there is no link / foreign key to the other table. Usually you would put department_id column in the employees table as a foreign key.
Last edited by Tabris; 03-31-2012 at 08:52 PM.
unomas
Banned
(03-31-2012, 08:53 PM)
unomas's Avatar

Originally Posted by Tabris

EDIT - Nevermind. I read it more closely.

You need something to join the tables on. That's a horrible schema if there is no link / foreign key to the other table. Usually you would put department_id column in the employees table as a foreign key.

Sorry, the employees table does have a department_id column. I'm just not sure how to join these tables/columns via subquery
Tabris
flaming jackass
(03-31-2012, 08:56 PM)
Tabris's Avatar
Oh, then just do this.

SELECT E.Employee_ID, E.Employee_Name
FROM Employees E
WHERE E.Department_ID IN
(SELECT Department_ID FROM Departments
WHERE Department_Name = 'Production')

Usually I would just write it as a join but your teacher wants you to do it as a subquery.
Last edited by Tabris; 03-31-2012 at 08:59 PM.
RoyalFool
(03-31-2012, 08:56 PM)
RoyalFool's Avatar
It sounds to me like you just want to join the tables together, rather than doing a sub-query..
ultron87
Member
(03-31-2012, 08:56 PM)
ultron87's Avatar
Presumably you don't want to use a join since the assignment specifically says to use a subquery.

You'll want to have the subquery grab the DepartmentID from the other table. And you can't select Department_ID or Department_Name from the main query and you shouldn't need to since that isn't what the assignment asks for.
unomas
Banned
(03-31-2012, 08:59 PM)
unomas's Avatar

Originally Posted by Tabris

Oh, then just do this.

SELECT E.Employee_ID, E.Employee_Name
FROM Employee E
WHERE E.Department_ID IN
(SELECT Department_ID FROM Departments
WHERE Department_Name = 'Production')

Usually I would just write it as a join but your teacher wants you to do it as a subquery.

Thanks, the first question of the assignment is do it as a join, the second question is to do the same exact thing as a subquery, I already did the join successfully and easily, but for some reason have been stuck on the subquery for the last 5 hours.......sad but true.

He also said it would be easier without using aliases, but you're definitely using aliases above which is fine by me. Thanks for your help, I've been banging my head against a wall for hours here.
unomas
Banned
(03-31-2012, 09:02 PM)
unomas's Avatar

Originally Posted by ultron87

Presumably you don't want to use a join since the assignment specifically says to use a subquery.

You'll want to have the subquery grab the DepartmentID from the other table. And you can't select Department_ID or Department_Name from the main query and you shouldn't need to since that isn't what the assignment asks for.

Correct, this is week 4 of the class and unfortunately I've had a rough time of things this week with some serious health issues, but our assignment is due tomorrow and I've been stuck on this second problem forever. I'll try to work through it on my own, thanks for the thoughts.
Tabris
flaming jackass
(03-31-2012, 09:02 PM)
Tabris's Avatar
5 hours? Woah man. Think of it like this. You can only grab columns from the tables within the FROM (and all joins from that from). You were trying to grab a column that didn't exist in Employees and then do the subquery based on that column. The only thing in Employees that matched with Departments was the id, so you want to do your subquery based on that.
unomas
Banned
(03-31-2012, 09:15 PM)
unomas's Avatar

Originally Posted by Tabris

5 hours? Woah man. Think of it like this. You can only grab columns from the tables within the FROM (and all joins from that from). You were trying to grab a column that didn't exist in Employees and then do the subquery based on that column. The only thing in Employees that matched with Departments was the id, so you want to do your subquery based on that.

Thanks, for your help, I really appreciate you taking the time to explain it as well. Now I realize why I was making the mistakes in the first place. Thanks

Thread Tools