How to compare 2 tables ??

Dev Shed Forums Sponsor:

July 1st, 2004, 09:31 AM

Dieter

Contributing User

Join Date: Apr 2003

Location: SA, Centurion

Posts: 305

Time spent in forums: 1 Day 5 h 16 m 26 sec
Reputation Power: 6

How to compare 2 tables ??

Hi,

Need help with the structuring of the following query:
I've got 2 tables nl :tableA, tableB
I need to do a query to determine wat DISTINCT values of a column(cIdNo) in tableA is not present in column (bIdNo) in tableB.

tableA contains Id numbers of people who still has to pay their bills, and tableB contains Id numbers of people who already paid. So I want a query to determine who still needs to pay.

I've tried the following but doesn't work, not to sure if it's the right wayto do it:

PHP Code:


		
			
 SELECT  distinct(cIdno) as IdNumber 

FROM tableA 

LEFT JOIN tableB

ON tableA.cIdNo != tableB.bIdNo

PLEASE HELP !! Thanks in advance !!!

__________________

July 1st, 2004, 09:41 AM

Dieter

Contributing User

Join Date: Apr 2003

Location: SA, Centurion

Posts: 305

Time spent in forums: 1 Day 5 h 16 m 26 sec
Reputation Power: 6

Sorry, been a long day... got it right eventually:

Here goes:

PHP Code:


		
			
 SELECT  distinct(cIdNo) as IdNumber 

FROM tableA 

WHERE tableA.cIdNo not in (Select distinct(bIdNo) from  tableB)

Any comments on the solution welcomed.

July 1st, 2004, 11:09 AM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,775

Time spent in forums: 2 Weeks 4 Days 16 h 24 m 58 sec
Reputation Power: 37

distinct is not a function. You dont need distinct when using in

Code:

SELECT  distinct cIdNo  as IdNumber
FROM tableA
WHERE cIdNo not in (Select bIdNo from  tableB)

July 2nd, 2004, 01:43 AM

Dieter

Contributing User

Join Date: Apr 2003

Location: SA, Centurion

Posts: 305

Time spent in forums: 1 Day 5 h 16 m 26 sec
Reputation Power: 6

Thanks for your comments

I need to make use of distinct values in the first select because this is NOT a unique field, however doing a distinct select in the 2nd is irrelevant.

The final query:

PHP Code:


		
			
 SELECT DISTINCT(cLocationCode) as Code

FROM MedAidLocation

WHERE MedAidlocation.cLocationCode

NOT IN

          (SELECT pkc_LocationCode

     FROM FeeSchedule)

Viewing: Dev Shed Forums > Databases > MS SQL Development > How to compare 2 tables ??

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump