Show not in - 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

Transact-SQL (2000)

Show not in

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-12-26 : 04:39:54

Good day to all...

I have this two tables tblEx and tbltrans



select tblEx.*
from (
	select '1' as a, '1' as b, 11 as Item union all
	select '2' as a, '1' as b, 22 as Item union all
	select '3' as a, '1' as b, 33 as Item union all
	select '4' as a, '1' as b, 44 as Item union all
	select '1' as a, '2' as b, 11 as Item union all
	select '2' as a, '2' as b, 11 as Item union all
	select '3' as a, '2' as b, 11 as Item union all
	select '5' as a, '2' as b, 55 as Item union all
	select '6' as a, '2' as b, 66 as Item union all
	select '7' as a, '2' as b, 77 as Item union all
	select '8' as a, '2' as b, 88 as Item union all 
	select '9' as a, '2' as b, 99 as Item union all 
	select '10' as a, '2' as b, 00 as Item union all 
	select '11' as a, '2' as b, 12 as Item 
	) as tblEx



select tbltrans.*
from (
	select '1' as a, '1' as b, 0 as isDeleted union all
	select '2' as a, '1' as b, 0 as isDeleted union all
	select '3' as a, '1' as b, 0 as isDeleted union all
	select '4' as a, '1' as b, 1 as isDeleted union all
	select '5' as a, '2' as b, 0 as isDeleted union all
	select '6' as a, '2' as b, 0 as isDeleted 
	) as tbltrans

What i want is to return the rows in tblEx which column a and b are not to be found in tbltrans.

Result should look like this...



select tblResult.*
from (
	select '1' as a, '2' as b, 11 as Item union all
	select '2' as a, '2' as b, 11 as Item union all
	select '3' as a, '2' as b, 11 as Item union all
	select '7' as a, '2' as b, 77 as Item union all
	select '8' as a, '2' as b, 88 as Item union all 
	select '9' as a, '2' as b, 99 as Item union all 
	select '10' as a, '2' as b, 00 as Item union all 
	select '11' as a, '2' as b, 12 as Item 
	) as tblResult

Want Philippines to become 1st World COuntry? Go for World War 3...

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-12-26 : 04:51:22

[code]

select tblEx.*
from (
select '1' as a, '1' as b, 11 as Item union all
select '2' as a, '1' as b, 22 as Item union all
select '3' as a, '1' as b, 33 as Item union all
select '4' as a, '1' as b, 44 as Item union all
select '1' as a, '2' as b, 11 as Item union all
select '2' as a, '2' as b, 11 as Item union all
select '3' as a, '2' as b, 11 as Item union all
select '5' as a, '2' as b, 55 as Item union all
select '6' as a, '2' as b, 66 as Item union all
select '7' as a, '2' as b, 77 as Item union all
select '8' as a, '2' as b, 88 as Item union all
select '9' as a, '2' as b, 99 as Item union all
select '10' as a, '2' as b, 00 as Item union all
select '11' as a, '2' as b, 12 as Item
) as tblEx

Left outer join
(
select tbltrans.*
from (
select '1' as a, '1' as b, 0 as isDeleted union all
select '2' as a, '1' as b, 0 as isDeleted union all
select '3' as a, '1' as b, 0 as isDeleted union all
select '4' as a, '1' as b, 1 as isDeleted union all
select '5' as a, '2' as b, 0 as isDeleted union all
select '6' as a, '2' as b, 0 as isDeleted
) as tbltrans

) As tbl2
on tbl2.a = tblEx.a and tblEx.b = tbl2.b
Where tbl2.a is null
[/code]

output
[code]
a b Item
---- ---- -----------
1 2 11
2 2 11
3 2 11
7 2 77
8 2 88
9 2 99
10 2 0
11 2 12

(8 row(s) affected)
[/code]

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-26 : 05:39:46

Select t1.*
from tblEX t1
where not exists (select * from tblTrans t2 where t2.a = t1.a and t2.b = t1.b)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources