WHERE NOT EXIST

MATTX

1)I NEED TO COMPARE TWO TABLE WITH THREE COLUMN TO VALIDATE

2)AND THE NEXT IS I NEED TO EXTRACT DATA WHERE NOT EXIST IN COMPARISON NO.1

SO HOW DO YOU WRITE THIS CODE IN MS SQL 2005

dhromed

So you have two tables, each with three columns, and you need to keep what they have in common, and remove everything else?

Just do a quick:

select into WonderfulDataComparisonTable (
select ta.col1, ta.col2, ta.col3 from tableA ta, tableB tb WHERE ta.col1 = tb.col1 AND ta.col2 = tb.col2 AND ta.col3 = tb.col3
)

SELECT INTO will create the table and its structure automatically. Adjust the names to your situation, of course. Look it up on MSDN (protip!).

Actually, that's off the top of my head. SQL is not my expertise, and I don't have SQL Studio here to verify.

PS.
Why did you write the entire post in ALL CAPS? Just curious, not upset. Maybe you have an input device like Stephen Hawking or something.

MATTX

actually this is my solution code..

SELECT [PMB1].[TranDate],[PMB1].[POSBranchCode],[PMB1].[MBBBranchCode],

[PMB1].[TranBranchCode],[PMB1].[SafteraID],[PMB1].[TranCode],

[PMB1].[AccNo],[PMB1].[RefNo],[PMB1].[TranAmount],[PMB1].[ProductName],[PMB1].[filename]

FROM [dbo].[pmb] AS [PMB1] WHERE [PMB1].[TranDate] BETWEEN @dateFrom AND @dateTo

AND [PMB1].[bil] NOT in(

SELECT [PMB].[bil]

FROM [dbo].[PMB] INNER JOIN [dbo].[MBB]

ON ([PMB].[AccNo] = [MBB].[AccNo] OR [PMB].[RefNo] = [MBB].[RefNo])

AND ([PMB].[TranAmount] = [MBB].[TranAmount])

)

i just select where column indiqator not in(another select statement that have same value on tblA &tblB)

so i got the data that not having the same value from both tblA & tblB

somedude

Might find a little cleaner-reading way to do it using a CTE, since you are using 2005.

ᐈ eSports News & Tricks (2024) 🥇 ENEWS.GG

belgariontheking

@MATTX said:

i just select where column indiqator not in(another select statement that have same value on tblA &tblB)

Looks about right. Another option is to do a right outer join and then below, say WHERE MBB.AccNo IS NULL AND MBB.RefNo IS NULL;

Which is faster may depend on your dataset. In mine, my suggested way proved faster.

On another note, is it a matter of preference, or do all those brackets have to be there? They make it mildly unreadable.

RaspenJho

FULL OUTER joins FTW

create table #table1 ( id1 int, id2 int, id3 int, somedata varchar(32))
create table #table2 ( id1 int, id2 int, id3 int, somedata varchar(32))
insert #table1 values ( 1, 1, 1, 'mojo' ), ( 2, 2, 2, 'my' ), (3, 3, 3, 'blah')

insert #table2 values ( 3, 3, 3, 'blah' ), (4, 4, 4, 'jojo' )
select	isnull(t1.id1, t2.id1) as id1,

isnull(t1.id2, t2.id2) as id2,

isnull(t1.id3, t2.id3) as id3,

isnull(t1.somedata, t2.somedata) as somedata

from	#table1 t1

full outer join	#table2 t2 on t1.id1 = t2.id1 and t1.id2 = t2.id2 and t1.id3 = t2.id3

where t1.id1 is null

or t2.id1 is null