WHERE NOT EXIST



  • 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



  • 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.



  • actually this is my solution code..

    <FONT color=#0000ff size=2><FONT color=#0000ff size=2>

    SELECT</FONT></FONT><FONT size=2> [PMB1]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[TranDate]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>,</FONT></FONT><FONT size=2>[PMB1]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[POSBranchCode]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>,</FONT></FONT><FONT size=2>[PMB1]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[MBBBranchCode]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>,

    </FONT></FONT><FONT size=2>

    [PMB1]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[TranBranchCode]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>,</FONT></FONT><FONT size=2>[PMB1]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[SafteraID]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>,</FONT></FONT><FONT size=2>[PMB1]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[TranCode]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>,

    </FONT></FONT><FONT size=2>[PMB1]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[AccNo]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>,</FONT></FONT><FONT size=2>[PMB1]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[RefNo]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>,</FONT></FONT><FONT size=2>[PMB1]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[TranAmount]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>,</FONT></FONT><FONT size=2>[PMB1]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[ProductName]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>,</FONT></FONT><FONT size=2>[PMB1]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[filename]</FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>FROM</FONT></FONT><FONT size=2> [dbo]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[pmb] </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>AS</FONT></FONT><FONT size=2> [PMB1] </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>WHERE</FONT></FONT><FONT size=2> [PMB1]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[TranDate] </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>BETWEEN</FONT></FONT><FONT size=2> @dateFrom </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>AND</FONT></FONT><FONT size=2> @dateTo

    </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>AND</FONT></FONT><FONT size=2> [PMB1]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[bil] </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>NOT</FONT></FONT><FONT size=2> </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>in(

    </FONT></FONT><FONT size=2></FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>SELECT</FONT></FONT><FONT size=2> [PMB]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[bil]</FONT></FONT><FONT size=2>

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>FROM</FONT></FONT><FONT size=2> [dbo]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[PMB] </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>INNER</FONT></FONT><FONT size=2> </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>JOIN</FONT></FONT><FONT size=2> [dbo]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[MBB]

    </FONT><FONT color=#0000ff size=2><FONT color=#0000ff size=2>ON</FONT></FONT><FONT size=2> </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>(</FONT></FONT><FONT size=2>[PMB]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[AccNo] </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>=</FONT></FONT><FONT size=2> [MBB]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[AccNo] </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>OR</FONT></FONT><FONT size=2> [PMB]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[RefNo] </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>=</FONT></FONT><FONT size=2> [MBB]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[RefNo]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>)</FONT></FONT><FONT size=2> </FONT></FONT><FONT size=2>

    </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>AND</FONT></FONT><FONT size=2> </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>(</FONT></FONT><FONT size=2>[PMB]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[TranAmount] </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>=</FONT></FONT><FONT size=2> [MBB]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>.</FONT></FONT><FONT size=2>[TranAmount]</FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>)

    </FONT></FONT><FONT size=2>

    </FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>)</FONT></FONT>

    <FONT color=#808080 size=2><FONT color=#808080 size=2>i just select where column indiqator not in(another select statement that have same value on tblA &tblB)</FONT></FONT>

    <FONT color=#808080 size=2><FONT color=#808080 size=2>so i got the data that not having the same value from both tblA & tblB

    </FONT></FONT>


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



  • @MATTX said:

    <font color="#808080" size="2"><font color="#808080" size="2">i just select where column indiqator not in(another select statement that have same value on tblA &tblB)</font></font>
    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.



  • 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


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.