How to filter out unwanted data

I have the following fields in table A:
Date | Descrip | Amt Dr
--
01/02/2006 | 740240 |(2,400.00)
14/02/2006 | 740241 |(3,000.00)
15/02/2006 | 41142 | 1,800.00
20/02/2006 | 41142 | 2,700.00
25/02/2006 | 740245 | 5,200.00
I have the following fields in table B:
Date | Descrip | Amt Dr
--
02/02/2006 |88258 | 1,400.00
17/02/2006 |740244 | (1,500.00)
25/02/2006 |740245 | 5,200.00
There are no referencial key between TableA & TableB,
What i want is to extract the date,descrip & Amt data from
Table A where it's Descrip data is not the same as the data
in Table B's Descrip column.
My sql syntax is as follows:
SELECT
CASE WHEN TableA.Amt < 0 THEN TableA.[Date] ELSE 0 END,
CASE WHEN TableA.Amt < 0 THEN TableA.Descrip ELSE 0 END,
CASE WHEN TableA.Amt < 0 THEN TableA.Amt - (TableA.Amt * 2)ELSE 0 END AS
[Add Back]
FROM TableA,TableB
WHERE TableA.Descrip <> TableB.Descrip
GROUP BY TableA.Date,TableA.Amt,TableA.Descrip
The output of the above is as follows:
Date | Descrip | Amt Dr
--
01/02/2006 | 740240 |(2,400.00)
14/02/2006 | 740241 |(3,000.00)
15/02/2006 | 0 | 0
20/02/2006 | 0 | 0
25/02/2006 | 740245 | 5,200.00
Notice that, descrip with 740245 appearing in both tables
is what the sql should filter out, but failed to do so,
what i want is as below:
Date | Descrip | Amt Dr
--
01/02/2006 | 740240 |(2,400.00)
14/02/2006 | 740241 |(3,000.00)
Can i achieve this? Please help.
*** Sent via Developersdex http://www.examnotes.net ***Dave
Untested
SELECT * FROM TableA WHERE NOT EXISTS
(SELECT * FROM TableB WHERE TableA.Descr<>Table.Descr)
"Dave dcartford" <dcartford@.gmail.com> wrote in message
news:ucygLFEaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>
> I have the following fields in table A:
> Date | Descrip | Amt Dr
> --
> 01/02/2006 | 740240 |(2,400.00)
> 14/02/2006 | 740241 |(3,000.00)
> 15/02/2006 | 41142 | 1,800.00
> 20/02/2006 | 41142 | 2,700.00
> 25/02/2006 | 740245 | 5,200.00
> I have the following fields in table B:
> Date | Descrip | Amt Dr
> --
> 02/02/2006 |88258 | 1,400.00
> 17/02/2006 |740244 | (1,500.00)
> 25/02/2006 |740245 | 5,200.00
> There are no referencial key between TableA & TableB,
> What i want is to extract the date,descrip & Amt data from
> Table A where it's Descrip data is not the same as the data
> in Table B's Descrip column.
> My sql syntax is as follows:
>
> SELECT
> CASE WHEN TableA.Amt < 0 THEN TableA.[Date] ELSE 0 END,
> CASE WHEN TableA.Amt < 0 THEN TableA.Descrip ELSE 0 END,
> CASE WHEN TableA.Amt < 0 THEN TableA.Amt - (TableA.Amt * 2)ELSE 0 END AS
> [Add Back]
> FROM TableA,TableB
> WHERE TableA.Descrip <> TableB.Descrip
> GROUP BY TableA.Date,TableA.Amt,TableA.Descrip
> The output of the above is as follows:
> Date | Descrip | Amt Dr
> --
> 01/02/2006 | 740240 |(2,400.00)
> 14/02/2006 | 740241 |(3,000.00)
> 15/02/2006 | 0 | 0
> 20/02/2006 | 0 | 0
> 25/02/2006 | 740245 | 5,200.00
> Notice that, descrip with 740245 appearing in both tables
> is what the sql should filter out, but failed to do so,
> what i want is as below:
>
> Date | Descrip | Amt Dr
> --
> 01/02/2006 | 740240 |(2,400.00)
> 14/02/2006 | 740241 |(3,000.00)
>
> Can i achieve this? Please help.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Is someone else posting from Uri's name.. juz joking :)
try this
SELECT * FROM TableA WHERE NOT EXISTS
(SELECT * FROM TableB WHERE TableA.Descr=Table.Descr)
"Uri Dimant" wrote:

> Dave
> Untested
> SELECT * FROM TableA WHERE NOT EXISTS
> (SELECT * FROM TableB WHERE TableA.Descr<>Table.Descr)
>
> "Dave dcartford" <dcartford@.gmail.com> wrote in message
> news:ucygLFEaGHA.1192@.TK2MSFTNGP04.phx.gbl...
>
>|||>> > Table A where it's Descrip data is not the same as the data
Just change NOT EXISTS to EXISTS
create table #table_a (c1 datetime,descr varchar(10))
create table #table_b (c1 datetime,descr varchar(10))
insert #table_a values ('20060101','a')
insert #table_a values ('20060101','h')
insert #table_a values ('20060101','r')
insert #table_b values ('20060101','a')
insert #table_b values ('20060101','c')
select * from #table_a where exists
(select * from #table_b where #table_a.descr=#table_b.descr)
drop table #table_a,#table_b
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:3F740FCC-A686-49F9-BF5C-7544C822E32D@.microsoft.com...
> Is someone else posting from Uri's name.. juz joking :)
> try this
> SELECT * FROM TableA WHERE NOT EXISTS
> (SELECT * FROM TableB WHERE TableA.Descr=Table.Descr)
> "Uri Dimant" wrote:
>|||Uri,
Shouldn't the query be like this' I apologise if I irritated you.
select * from #table_a where not exists
(select * from #table_b where #table_a.descr=#table_b.descr)|||Hi
Doh, I need a cofee , my mistake. I don't why ,i have been assumed that he
needs the same descr to get out
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:6A886FB3-F225-4863-A453-C0E6C07F1819@.microsoft.com...
> Uri,
> Shouldn't the query be like this' I apologise if I irritated you.
> select * from #table_a where not exists
> (select * from #table_b where #table_a.descr=#table_b.descr)
>