Query help - 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)

Query help

Author

Topic

supersql
Yak Posting Veteran

99 Posts

Posted - 2011-04-08 : 07:00:06

Can some one help me with this qry?

ALTER VIEW [dbo].[empcheck]
AS
(
SELECT Col1,Col1...Col3
FROM empdet_OCC WHERE empscope IN ('US','UK')
UNION ALL
SELECT Col1,Col1...Col3
from empdet_OTC where empscope IN ('US', 'UK') 
)as P left join emplookup.dbo.OBVR_emprangeno AG 
ON P.emprangeno=AG.emprangeno where AG.OBVR_never in ('345','765') and AG.empstat in ('YES','NA')

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-08 : 07:07:34

ALTER VIEW [dbo].[empcheck]
AS SELECT Col1,
Col1...Col3
FROM empdet_OCC
WHERE empscope IN ( 'US', 'UK' )
UNION ALL
SELECT *
FROM ( SELECT Col1,
Col1...Col3
from empdet_OTC
where empscope IN ( 'US', 'UK' )
) as P
left outer join emplookup.dbo.OBVR_emprangeno AG ON P.emprangeno = AG.emprangeno
where AG.OBVR_never in ( '345', '765' )
and AG.empstat in ( 'YES', 'NA' )

--------------------------
http://connectsql.blogspot.com/

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-08 : 07:08:38

Keep in mind that UNION will not work if both queries have different number of columns (as in second query on union i have used * )

--------------------------
http://connectsql.blogspot.com/

supersql
Yak Posting Veteran

99 Posts

Posted - 2011-04-08 : 07:18:58

yes, i understand that and so i have same no of columns in the first 2 queries but the result of those 2 queries has to be joined with the 3rd query.

another question i have is can i use "IN" instead if left join. the goal here is to pull all emprangeno's from first 2 queries which are not in (select emprangeno where empstat in ( 'YES', 'NA' ))

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-08 : 07:56:52

yeah you can

WHERE P.emprangeno NOT IN (SELECT emprangeno
FROM emplookup.dbo.OBVR_emprangeno
where OBVR_never in ( '345', '765' )
and empstat in ( 'YES', 'NA' ))

--------------------------
http://connectsql.blogspot.com/

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-04-08 : 08:04:01

quote:

Originally posted by supersql

Can some one help me with this qry?

ALTER VIEW [dbo].[empcheck]
AS
(
SELECT Col1,Col1...Col3
FROM empdet_OCC WHERE empscope IN ('US','UK')
UNION ALL
SELECT Col1,Col1...Col3
from empdet_OTC where empscope IN ('US', 'UK') 
)as P left join emplookup.dbo.OBVR_emprangeno AG 
ON P.emprangeno=AG.emprangeno where AG.OBVR_never in ('345','765') and AG.empstat in ('YES','NA')

How can it work? is it not giving this kind of error 'Msg 156, Level 15, State 1, .....
Incorrect syntax near the keyword 'as'.'

check it once

ALTER VIEW [dbo].[empcheck]
AS
SELECT p.* FROM (
SELECT Col1,Col1...Col3
FROM empdet_OCC WHERE empscope IN ('US','UK')
UNION ALL
SELECT Col1,Col1...Col3
from empdet_OTC where empscope IN ('US', 'UK')
)as P left join emplookup.dbo.OBVR_emprangeno AG
ON P.emprangeno=AG.emprangeno where AG.OBVR_never in ('345','765') and AG.empstat in ('YES','NA')

sathish

supersql
Yak Posting Veteran

99 Posts

Posted - 2011-04-08 : 08:30:19

I am simplying the query to make it easy here. It works this way but its not returning any records may be becasue of join

ALTER VIEW [dbo].[empcheck]
AS
SELECT * FROM (
SELECT Col1,Col1,Col3 FROM empdet_OCC
UNION ALL
SELECT Col1,Col1,Col3 from empdet_OTC
) as P left join emplookup.dbo.OBVR_emprangeno AG
ON P.emprangeno=AG.emprangeno where P.OBVR_never in ('345','765') and AG.empstat not in ('YES','NA')

But this is what i need, hope you understand where i am trying to get.

ALTER VIEW [dbo].[empcheck]
AS
SELECT Col1,Col1,Col3 FROM empdet_OCC where OBVR_never in ('345','765')
UNION ALL
SELECT Col1,Col1,Col3 from empdet_OTC where OBVR_never in ('345','765')
where emprangeno not in (select emprangeno where empstat not in ('YES','NA')

supersql
Yak Posting Veteran

99 Posts

Posted - 2011-04-08 : 10:02:27

can i use NOT EXISTS in this scenario?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources