Joining with sub queries - A challenge

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

General SQL Server Forums

New to SQL Server Programming

Joining with sub queries - A challenge

Author

Topic

sheriefes
Starting Member

9 Posts

Posted - 2010-12-23 : 14:11:05

Hi

I hav a 2 tables

ownSales with fields -
invoiceNo, invoicedate, purchaser,amount

otherSales with fields -
invoiceNo, invoicedate, purchaser,amount

ownSales contains records of sales made by A

ex:
invoiceNo invoicedate purchaser amount

4 10-12-10 B&Co 1000
5 11-12-10 C&Co 2000
6 12-12-10 D&Co 3000

ie., as per invoiceNo 4, A sold to B&Co goods worth $1000
as per invoiceNo 6, A sold to D&Co goods worth $3000

otherSales contains records of purchases made dealers from A

ex
invoiceNo, invoicedate, seller,amount

A4 10-12-10 B&Co 1000
5-11/12 11-12-10 C&Co 2000
6 12-12-10 D&Co 3000

ie., as per invoiceNo A4, B&Co purchased goods worth $1000 from A
as per invoiceNo 6, D&Co purchased goods worth $3000 from A

Now come to the point:
1. We have sales details declared by A & also details of purchases from A, by other dealers

2. We want to cross verify - whether A's declaration is full and correct (from the declaratio by other dealsers, that they made such purchases from A)

SELECT * FROM otherSales WHERE invoiceNo
NOT IN(SELECT invoiceNo FROM ownSales)

will surely an answer

3. But, the problem is sometimes other dealers (B&CO, C&Co etc)
not giving the invoiceNo as exactly as given by A - they make little alterations, see othersales records

ex;, in the case given above, the above query returns 2 records
which is incorrect.

4. that means, we must check amount, date, purchaser also
as a lenient way( before reaching a conclusion A made sales suppressions)

5. I want query for that? any idea

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-12-23 : 14:50:26

Duplicate post. No replies to this thread please. Direct replies to:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=154502[/url]

--
Gail Shaw
SQL Server MVP

sheriefes
Starting Member

9 Posts

Posted - 2010-12-25 : 23:43:04

No, Puzzle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources