NOT IN query problem - 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 2000 Forums

SQL Server Administration (2000)

NOT IN query problem

Author

Topic

Keshaba
Yak Posting Veteran

52 Posts

Posted - 2010-06-12 : 02:12:58

I got a problem in writing NOT IN query
It works fine when i write not in query like this :

1)select distinct P5.pay_head_name from P5 where payroll_type<>'X' AND P5.pay_head_name NOT IN(select distinct P5.pay_head_name from P5 where payroll_type='X') (This is fine)

But when i try to include more columns it give me error like

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

I tried like like this

2)select distinct P5.pay_head_name,P5.payroll_type from P5 where payroll_type<>'X' AND P5.pay_head_name NOT IN(select distinct P5.pay_head_name,P5.payroll_type from P5 where payroll_type='X')

what is the problem in my query no --2.Can anybody help

Keshab

Kristen
Test

22859 Posts

Posted - 2010-06-12 : 02:32:50

You can only select a SINGLE column in the SELECT statement inside an IN / NOT in list

So you can do

NOT IN(select distinct P5.pay_head_name FROM ...

but you cannot do

NOT IN(select distinct P5.pay_head_name, P5.payroll_type FROM ...

Why do you wantthe P5.payroll_type column in the NOT IN list?

Keshaba
Yak Posting Veteran

52 Posts

Posted - 2010-06-12 : 02:48:35

payrolltype is required Because I am keeping different payheads under
different payrolltype.

This query helped me a bit ..
select distinct P5.pay_head_id,P5.pay_head_name,P5.payroll_type,P5.addition_deduction from P5 where payroll_type<>'X' AND P5.pay_head_name NOT IN(select distinct P5.pay_head_name from P5 where payroll_type='X')UNION(select distinct P5.pay_head_id,P5.pay_head_name,P5.payroll_type,P5.addition_deduction from P5 where payroll_type='X')

But unable to bring payrolltype which is NULL

Keshab

Kristen
Test

22859 Posts

Posted - 2010-06-12 : 04:36:56

"payrolltype is required Because I am keeping different payheads under different payrolltype"

OK, but you will get the same payheads listed by

select distinct P5.pay_head_name from P5 where payroll_type='X'

as you will by

select distinct P5.pay_head_name,P5.payroll_type from P5 where payroll_type='X'

so including the payroll_type in the SELECT list doesn't make any difference to whether the pay_head_name is in the list, or not.

Is this what you want?


select distinct P5.pay_head_id, P5.pay_head_name, P5.payroll_type, P5.addition_deduction 
from P5
where
(
         payroll_type<>'X' 
     AND P5.pay_head_name NOT IN
    (
        select distinct P5.pay_head_name 
        from P5 
        where payroll_type='X'
              AND P5.pay_head_name IS NOT NULL
    )
)
    OR payroll_type='X'
    OR payroll_type IS NULL

the last bit will also include records where the payroll_type IS NULL[

NOTE: If you have a P5.pay_head_name which is NULL then your NOT IN list will fail, so I've added that "just in case it ever happens"

Kristen
Test

22859 Posts

Posted - 2010-06-12 : 04:40:47

Actually, using NOT EXISTS would be more efficient:


select distinct P5.pay_head_id, P5.pay_head_name, P5.payroll_type, P5.addition_deduction 
from P5 AS P5_A
where
(
         payroll_type<>'X' 
     AND NOT EXISTS
    (
        select *
        from P5 AS P5_B
        where     P5_B.payroll_type='X'
              AND P5_B.pay_head_name = P5_A.pay_head_name
    )
)
    OR payroll_type='X'
    OR payroll_type IS NULL

Keshaba
Yak Posting Veteran

52 Posts

Posted - 2010-06-12 : 06:19:14

Yes It was exactly what i want.You have read my mind.But I want to order it in the following way.If i give X as payrolltype I want the payroll_type X should come first and others following them.Can anything be done like that .. I have used this query..

select distinct view_ptype.pay_head_id, view_ptype.pay_head_name, view_ptype.payroll_type, view_ptype.addition_deduction from view_ptype where(payroll_type<>'X' AND view_ptype.pay_head_name NOT IN(select distinct view_ptype.pay_head_name from view_ptype where payroll_type = 'X' AND view_ptype.pay_head_name IS NOT NULL))OR payroll_type='X' OR payroll_type IS NULL group by pay_head_id

please help

Keshab

Kristen
Test

22859 Posts

Posted - 2010-06-12 : 09:23:31

"I want the payroll_type X should come first and others following them"


ORDER BY
    CASE WHEN payroll_type = 'X' THEN 1 ELSE 2 END,
    ... any other sort columns here ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources