Nested SELECT statement error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Nested SELECT statement error

Author

Topic

dehahnes
Starting Member

5 Posts

Posted - 2011-02-25 : 07:55:18

Hi,

I am trying to run to following query:


SELECT     groupschool.groupschoolid, orderdetail.productid
FROM       groupschool 
WHERE     (groupschool.status = 'Active') 
           AND (groupschool.groupschoolid NOT IN
                           (SELECT     orderdetail.groupschoolid
                            FROM          orderdetail
                            WHERE      (orderdetail.productid = '5')))

This result in the following error:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "orderdetail.productid" could not be bound.

Where am I going wrong?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-25 : 08:05:43

[code]SELECT groupschool.groupschoolid, orderdetail.productid[/code]
you can't reference orderdetail.productid which is in the subquery

KH
[spoiler]Time is always against us[/spoiler]

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-25 : 08:41:27

But if you want to have this column then you will need to Join groupschool table with orderdetail via Left Join

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-02-26 : 09:41:35

[code]
SELECT groupschool.groupschoolid, orderdetail.productid
FROM groupschool
INNER JOIN orderdetail
ON orderdetail.groupschoolid = orderdetail.groupschoolid
LEFT JOIN (SELECT orderdetail.groupschoolid
FROM orderdetail
WHERE (orderdetail.productid = '5'))orderdetail1
ON orderdetail1.groupschoolid = orderdetail.groupschoolid
WHERE (groupschool.status = 'Active')
AND orderdetail1.groupschoolid IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dehahnes
Starting Member

5 Posts

Posted - 2011-02-26 : 09:48:14

Many thanks!!

Issue solved!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-02-26 : 09:52:04

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources