Finding Unmatched Records With Two Tables

[jjc3397]

I have two tables which has been brought in from Microsoft Access. Table 1 is OUTL0607A and Table 2 is OUTL0607B.
The two tables have been linked together using the keys, State Code, and Store Number which are fields in both tables. The difference between the two tables is about 810 records. I need to find matches between the two Tables. I am trying to do a Sequel View by placing these two tables in the SQL Management Express. I am using a SELECT DISTINCT. I need for the query to pull the records that have matches for State Number and State Code but I only want the records to show only once and not be duplicated.

Table 1 has 91,810 records and Table 2 Has 91,000 records so I know there have been some field added amounting to 810 records.

Any help is appreciated.

Thanks

jjc3397

 

[bborissov]

SELECT OUTL0607A.*
    FROM OUTL0607A
INNER JOIN (SELECT [State Code], [Store Number]
                   FROM OUTL0607B
                   GROUP BY [State Code], [Store Number]
                   HAVING COUNT(*) = 1) Tbl1
ON OUTL0607A.[State Code]   = Tbl1.[State Code]
   OUTL0607A.[Store Number] = Tbl1.[Store Number]

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

That query will works only if OUTL0607B have more records that OUTL0607A (WOW what the names). Also after I re-read the title and the post I become a little confused. Can you post some simple data and what you want from it?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jjc3397]

OUTL0607A has these sample records:

LicType StoreNumber StateCode BottleSales PackSize

010 001 E0112 25 12
011 001 E2345 30 6

This same data is broken out with same field names in both tables. There have been some additonal records added to OUTL0607A that are not in OUTL0607B whihc amounts to 810 records. I need to know which records they are.

What I am tryiong to do with the results is compute the total cases sold for these 810 records by taking the Bottle Sales/PackSize and they giving a gradn total Cases Sold.

 

[bborissov]

Can you post a simple data from BOTH tables and how the result must looks like?
If you have this in OUTL0607A:

LicType    StoreNumber  StateCode  BottleSales  PackSize

010           001        E0112        25           12
011           001        E2345        30            6

and this in OUTL0607B:

StoreNumber  StateCode

001        E0112
001        E0112
001        E0112
001        E0112
001        E0112
001        E2345

What you want as a result?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jjc3397]

E0112 is a Code for a Product. This code can be sold in 1 to 160 stores which has a Storenumber. Say this is widgets. I could have widgets sold at several stores on every date of the month. On July 10th last week I used the OUTL0607B File to run totals. There were new records added on July 12th after I ran my totals. These new records which anmount to 810 records contain new state codes that are not in the old table. OUTL0607A has these new StateCodes with Bottle Sales by Pack. I need to know the New TOTAL CASEs for these Product codes that were added. Yes I need just the matches one time found in both tables. We are short 1,200 cases in the amounts I sent using OUTL0607B. These 1,200 cases sold represent the new records added on July 12th.

Does this make sense.

 

[bborissov]

jjc3397,
Please do what I asked for. Post some data from BOTH tables and what you want as a result from that data. There is no need to post whole data (90K records here, grrrr) Just enough that could help me to understand what you want to do. In your last post you talk about date, Can't you filter your records by it?


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jjc3397]

OK, Sorry

OUTL0607A Sample Records:

LincenseType LicenseNumber CountyNumber StoreNumber StateCode TradeName
010 0001 49 85 E3911 DEUCES WILD
010 0006 61 27 F0114 HEAVY'S LOUNGE
010 0006 61 27 F0152 HEAVY'S LOUNGE
010 0006 61 27 F0305 HEAVY'S LOUNGE
010 0006 61 27 F0410 HEAVY'S LOUNGE
010 0006 61 27 F0453 HEAVY'S LOUNGE
010 0006 61 27 F0664 HEAVY'S LOUNGE
010 0006 61 27 F0675 HEAVY'S LOUNGE
010 0011 11 46 A0114 THE SKYLIGHT BAR
010 0011 11 46 A0165 THE SKYLIGHT BAR
010 0011 11 46 A0256 THE SKYLIGHT BAR
010 0011 11 46 A0262 THE SKYLIGHT BAR
010 0011 11 46 A0305 THE SKYLIGHT BAR
010 0011 11 46 A0306 THE SKYLIGHT BAR
010 0011 11 46 A0393 THE SKYLIGHT BAR
010 0011 11 46 A0396 THE SKYLIGHT BAR
010 0011 11 46 A0418 THE SKYLIGHT BAR
010 0011 11 46 A0439 THE SKYLIGHT BAR
010 0011 11 46 A0495 THE SKYLIGHT BAR



OUTL0607B

LincenseType LicenseNumber CountyNumber StoreNumber StateCode TradeName
010 0011 11 46 E0137 THE SKYLIGHT BAR
010 0011 11 46 E0152 THE SKYLIGHT BAR
010 0011 11 46 E0254 THE SKYLIGHT BAR
010 0011 11 46 E0305 THE SKYLIGHT BAR
010 0011 11 46 E0393 THE SKYLIGHT BAR
010 0011 11 46 E0410 THE SKYLIGHT BAR
010 0011 11 46 E0418 THE SKYLIGHT BAR
010 0011 11 46 E0578 THE SKYLIGHT BAR
010 0011 11 46 E0628 THE SKYLIGHT BAR
010 0011 11 46 E0664 THE SKYLIGHT BAR
010 0011 11 46 E0823 THE SKYLIGHT BAR
010 0011 11 46 E1148 THE SKYLIGHT BAR
010 0011 11 46 E1185 THE SKYLIGHT BAR
010 0011 11 46 F3898 THE SKYLIGHT BAR
010 0012 46 116 A0018 ESCAPADES
010 0012 46 116 A0256 ESCAPADES
010 0012 46 116 A0393 ESCAPADES
010 0012 46 116 A0404 ESCAPADES
010 0012 46 116 A0467 ESCAPADES
010 0012 46 116 E0038 ESCAPADES
010 0012 46 116 E0084 ESCAPADES
010 0012 46 116 E0114 ESCAPADES
010 0012 46 116 E0137 ESCAPADES
010 0012 46 116 E0305 ESCAPADES
010 0012 46 116 E0675 ESCAPADES
010 0012 46 116 F3911 ESCAPADES
010 0012 46 116 F3926 ESCAPADES
010 0016 02 43 A0165 HOMEPORT
010 0016 02 43 A0439 HOMEPORT
010 0016 02 43 E0024 HOMEPORT
010 0016 02 43 E0114 HOMEPORT
010 0016 02 43 E0201 HOMEPORT
010 0016 02 43 E0250 HOMEPORT
010 0016 02 43 E0305 HOMEPORT
010 0016 02 43 E0363 HOMEPORT
010 0016 02 43 E0393 HOMEPORT

Hope this helps

 

[bborissov]

And from that data you want?
Which records from OUTL0607B must be inculded in the result?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jjc3397]

All the records must be included in the result. I want just the new records shown only once.

 

[jjc3397]

The Table OUTL0607A will have records by new stores that have been added. These new stores added should not be in the Old Table OUTL0607, but the Other fields will need to show for State Code and Bottles Sales. The State Codes indicating products sold should show for these new stores added to OUTL0607A and not be in the Old Table OUTL0607