SQL Server: how to select sub-query column in main query

Question

I have a query to select particular day-time for a office.

select 
    a.sat_date, 
    b.officeid 
from  
    OfficeHours a 
where 
    sat_date = '9:30 AM'
    and officeik in (select OfficeIK from office where officeid = 50000) b

I need to select the sub-query column officeid in main query. Above query throws a syntax error.

Thanks for the help.

not defined alias a and what's the error you are getting?

Ullas
– Ullas

2015-12-10 07:49:06 +00:00
Commented Dec 10, 2015 at 7:49 — Ullas
– Ullas, Commented Dec 10, 2015 at 7:49
I think b.officied -> b.officeid

Joe Taras
– Joe Taras

2015-12-10 07:49:41 +00:00
Commented Dec 10, 2015 at 7:49 — Joe Taras
– Joe Taras, Commented Dec 10, 2015 at 7:49
I have edited the questions. thanks for pointing out

bmsqldev
– bmsqldev

2015-12-10 07:51:53 +00:00
Commented Dec 10, 2015 at 7:51 — bmsqldev
– bmsqldev, Commented Dec 10, 2015 at 7:51

Andrey Korneyev · Accepted Answer · 2015-12-10 07:50:41Z

6

You can't use officied column from subquery not only because that subquery select list doesn't contain this column, but also bu the reason it is in where condition, not in some join/apply.

Instead you can join that subquery and use it columns like this:

select 
    a.sat_date,
    b.officied 
from OfficeHours as a
    inner join (select * from office where officeid = 50000) as b on b.officeik = a.officeik
where a.sat_date = '9:30 AM'

or (even simplier and more natural):

select 
    a.sat_date,
    b.officied 
from OfficeHours as a
    inner join office as b on b.officeik = a.officeik
where 
    a.sat_date = '9:30 AM'
    and b.officeid = 50000

answered Dec 10, 2015 at 7:50

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

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

2 Comments

bmsqldev Over a year ago

thanks for the solution. but clear on the point "subquery select list doesn't contain this column". I am selecting all the columns in the subquery table

Andrey Korneyev Over a year ago

@bmsqldev your subquery is (select OfficeIK from office... and not (select OfficeIK, officeid from office, so even if it was in join - you can't use column you're not included into select list since it will not be present in query result

Dan Sin · Accepted Answer · 2015-12-10 07:52:01Z

1

You can use inner join:

select a.sat_date ,b.officied
 from OfficeHours a inner join office b on(a.officeik=b.OfficeIK)
  where a.sat_date = '9:30 AM' and b.officeid=50000

answered Dec 10, 2015 at 7:52

Dan Sin

4914 silver badges10 bronze badges

Comments

Nguyễn Hải Triều · Accepted Answer · 2015-12-10 07:55:03Z

1

You can use INNER JOIN, if you want to use sub-query, you can try this:

select a.sat_date, 50000 AS officeid
from OfficeHours a
where sat_date = '9:30 AM'
   and officeik in
   (select OfficeIK from office where officeid = 50000)

answered Dec 10, 2015 at 7:55

Nguyễn Hải Triều

1,4641 gold badge8 silver badges15 bronze badges

Collectives™ on Stack Overflow

SQL Server: how to select sub-query column in main query

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related