Subquery question - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Subquery question

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-02-13 : 10:03:05

I need to get a column from the subquery table but i am getting the following error. what should the syntax be?

Select p.*, t.Tax_ID2
into #Temp1
FROM MyTable p
where exists (select MyTable_t_ID, MAX(Tax_ID2) Tax_ID2
from MyTable_t t
where p.MyTable_ID = t.MyTable_t_ID
group by MyTable_t_ID)

error:

Msg 207, Level 16, State 1, Line 4
Invalid column name 'Tax_ID2'.
Msg 4104, Level 16, State 1, Line 4
The multi-part identifier "t.Tax_ID2" could not be bound.

Sara Karasik
Starting Member

10 Posts

Posted - 2008-02-13 : 10:44:53

You want an inner join, not an exists. You cannot reference the field t.Tax_ID2 if it is not joined, only used in the where clause.

try this.

Select p.*, t.Tax_ID2
into #Temp1
FROM MyTable p
join (select MyTable_t_ID, MAX(Tax_ID2) Tax_ID2
from MyTable_t t
group by MyTable_t_ID) dt
where p.MyTable_ID = dt.MyTable_t_ID

Using a Derived Table will give you the results you want.

Please let me know if this helped.

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-02-13 : 11:11:52

I tried the suggested code on my actual tables (my test tables were killed by someone), so the table names are different. I get the followint error.

Select p.*, dt.Tax_ID2
into #Temp1
FROM phypro_Current p
join (select PhyPro_ID, MAX(Tax_ID2) Tax_ID2
from phypro_Current_t t
group by t.PhyPro_ID) dt
where p.PhyPro_ID = dt.PhyPro_ID

Error:

Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'where'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-13 : 11:19:15

Change like this:-

Select p.*, dt.Tax_ID2
into #Temp1
FROM phypro_Current p
inner join (select PhyPro_ID, MAX(Tax_ID2) Tax_ID2 
      from phypro_Current_t t 
      group by t.PhyPro_ID) dt 
on dt.PhyPro_ID = p.PhyPro_ID

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-02-13 : 11:40:02

That worked. My thanks to both of you. I learn some thing new every time I come here. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources