Error when using nested selects in sql

Question

I am trying to do a simple exercise for slq where i have to get as much info as i can about poeple who work in japan in my database. However i just started learning so i don't even know what to google to answer my problem. So here it is:

My Code:

SELECT *
FROM Employees
WHERE DEPARTMENT_ID = 
    (SELECT *
    FROM Departments
    WHERE LOCATION_ID = 
        (SELECT *
        FROM Locations
        WHERE Country_ID = 
            (SELECT *
            FROM Countries
            WHERE Country_Name = 'Japan')))

My Error:

Msg 116, Level 16, State 1, Line 12
Only one expression can be specified in the select list when the subquery is not introduced with     EXISTS.
Msg 116, Level 16, State 1, Line 12
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Msg 116, Level 16, State 1, Line 12
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

My Database: https://i.sstatic.net/ar0aT.png

EDIT: I think i hit a wall... https://i.sstatic.net/FjTps.png

This looks like a potential performance nightmare. You should reconsider rewriting this with joins. — Yatrix
– Yatrix, Commented Sep 12, 2014 at 21:18
I did hear something about joins, however i am not concerned about performance as no tables have more than 100 rows. — Kevin
– Kevin, Commented Sep 12, 2014 at 21:23

Rahul Tripathi · Accepted Answer · 2014-09-12 21:25:46Z

3

You are using select * in your nested selects. You need to select a particular column instead.

Something like this:

SELECT *
FROM Employees
WHERE DEPARTMENT_ID IN 
    (SELECT DEPARTMENT_ID
    FROM Departments
    WHERE LOCATION_ID IN 
        (SELECT LOCATION_ID
        FROM Locations
        WHERE Country_ID IN 
            (SELECT Country_ID
            FROM Countries
            WHERE Country_Name = 'Japan')))

The same query using JOINS which are efficient.

SELECT *
FROM Employees
INNER JOIN Departments ON Employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID
INNER JOIN Locations ON Departments.LOCATION_ID = Locations.Location_ID
INNER JOIN Countries ON Locations.Country_ID = Countries.Country_ID
WHERE Countries.Country_Name = 'Japan'

edited Sep 12, 2014 at 21:25

answered Sep 12, 2014 at 21:16

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Rahul Tripathi Over a year ago

@Kevin:- Also do note that I have used IN instead of = as I assume that queries can return more than one row. Thanks :)

Kevin Over a year ago

You are right that they retuen more than one row, i tried it with = and i got "Subquery returned more than 1 value." Error. However i am not getting any results when i try it with IN.

Rahul Tripathi Over a year ago

@Kevin:- I suggest you to better use JOINS instead of nested selects

Kevin Over a year ago

I heard about joins before but i didn't look into them yet, is there a way to do it without them, not a big deal just curious.

Kevin Over a year ago

Thanks again for all the help, at least now i know whats wrong!

|

Nick H. · Accepted Answer · 2014-09-12 21:23:46Z

1

You cannot use "SELECT * " inside IN statements. Change your query to this:

SELECT *
FROM Employees
WHERE DEPARTMENT_ID IN
    (SELECT DEPARTMENT_ID
    FROM Departments
    WHERE LOCATION_ID = 
        (SELECT LOCATION_ID
        FROM Locations
        WHERE Country_ID = 
            (SELECT Country_ID
            FROM Countries
            WHERE Country_Name = 'Japan')))

edited Sep 12, 2014 at 21:23

answered Sep 12, 2014 at 21:17

Nick H.

1,61614 silver badges20 bronze badges

2 Comments

Kevin Over a year ago

I didnt know that it would be a problem becuase i dont know the limitations of = but i get an error because i am returning mor than one row.

Nick H. Over a year ago

Change the "=" to IN R.T. found it first. Props.

TechneWare · Accepted Answer · 2014-09-12 21:26:00Z

0

I would write it using joins. Much cleaner.

SELECT  e.*
FROM    Employees e
        JOIN Departments d ON e.Department_ID = d.id
        JOIN Locations l ON d.location_id = l.id
        JOIN Countries c ON l.country_id = c.id
WHERE   c.Country_Name = 'Japan'

answered Sep 12, 2014 at 21:26

TechneWare

2631 silver badge7 bronze badges

Comments

Marc Zellick · Accepted Answer · 2014-09-12 21:38:44Z

0

You need to Select the ID instead of * on the Subqueries. You should definitely learn to write it out this way so you know how to use subqueries. However in reality for this type of query you should be using JOINs. Here's an example of how to do it that way:

SELECT Employees.* FROM Employees INNER JOIN Departments ON Employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID INNER JOIN Locations ON Departments.LOCATION_ID = Locations.Location_ID INNER JOIN Countries ON Locations.Country_ID = Countries.Country_ID WHERE Countries.Country_Name = 'Japan'

edited Sep 12, 2014 at 21:38

answered Sep 12, 2014 at 21:24

Marc Zellick

263 bronze badges

Collectives™ on Stack Overflow

Error when using nested selects in sql

4 Answers 4

7 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

7 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related