Re: Unique record match with multiple possibilities

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Jacco Schalkwijk (NOSPAMjaccos_at_eurostop.co.uk)
Date: 05/06/04 

Date: Thu, 6 May 2004 22:50:32 +0100

Hi BG,

The following solution uses derived tables (see SQL Server Books Online for
more details) to create an ordering in for the J1 and J3 within their
respective X1 and X2. From your description I gather that you have to match
the lowest J1 in an X1 with the lowest J3 in the corresponding X2, and them
the next lowest etc. I have used only the relevant columns, leaving out
description etc, and only a limited amount of data.

See the script below for the solution and check the intermediate results, as
these will probably help you understand the code better: 

-- 
Jacco Schalkwijk
SQL Server MVP
CREATE TABLE table1 (J1 CHAR(2), X1 CHAR(1))
CREATE TABLE table2 (J3 CHAR(2), X2 CHAR(1))
GO
INSERT INTO table1(J1, X1) VALUES('01', 'A')
INSERT INTO table1(J1, X1) VALUES('02', 'A')
INSERT INTO table1(J1, X1) VALUES('03', 'A')
INSERT INTO table1(J1, X1) VALUES('04', 'A')
INSERT INTO table1(J1, X1) VALUES('05', 'B')
INSERT INTO table1(J1, X1) VALUES('06', 'B')
INSERT INTO table1(J1, X1) VALUES('07', 'B')
INSERT INTO table1(J1, X1) VALUES('08', 'B')
INSERT INTO table2(J3, X2) VALUES('04', 'A')
INSERT INTO table2(J3, X2) VALUES('05', 'A')
INSERT INTO table2(J3, X2) VALUES('08', 'A')
INSERT INTO table2(J3, X2) VALUES('10', 'A')
INSERT INTO table2(J3, X2) VALUES('07', 'B')
INSERT INTO table2(J3, X2) VALUES('09', 'B')
GO
-- intermediate results:
SELECT J1, X1, (SELECT COUNT(*) FROM table1 ct1 WHERE ct1.X1 = t1.X1 AND
ct1.J1 < = t1.J1) AS order_no
FROM table1 t1
SELECT J3, X2, (SELECT COUNT(*) FROM table2 ct2 WHERE ct2.X2 = t2.X2 AND
ct2.J3 < = t2.J3) AS order_no
FROM table2 t2
-- full result with derived tables:
SELECT r1.J1, r2.J3
FROM (
SELECT J1, X1, (SELECT COUNT(*) FROM table1 ct1 WHERE ct1.X1 = t1.X1 AND
ct1.J1 < = t1.J1) AS order_no
FROM table1 t1
) AS r1
LEFT OUTER JOIN
(
SELECT J3, X2, (SELECT COUNT(*) FROM table2 ct2 WHERE ct2.X2 = t2.X2 AND
ct2.J3 < = t2.J3) AS order_no
FROM table2 t2
) AS r2
ON r1.X1 = r2.X2
AND r1.order_no = r2.order_no
ORDER BY r1.J1
GO
DROP TABLE table1, table2
"BG" <anonymous@discussions.microsoft.com> wrote in message
news:483EEE29-D426-41E0-8661-2FC34FD8D0C7@microsoft.com...
> Jacco,
>
> Sorry for the confusion.
>
> Table1 consists of 4 fields: X1, J2, Description, and J1.
> Table2 consists of 4 fields: X2, Descr, J3, and Data.
>
> I am trying to match Connector Pins in an Electrical Wiring Diagram for
Connectors J1 and J3 (Table1.J1 and Table2.J3) using the fields Table1.X1 =
Table2.X2.
> I can only connect 1 wire to an individual Pin; therefore each record in
Table1 can have a maximum of 1 record match in Table2.
>
> Table1 will ALWAYS have more records than Table2.
>
> The approximate result will be:
>
> J2             Description                 J1            X1            X2
Descr                        J3             Data
>
> J2-A01      Description A             01            A              A
Description 04            04            Data 04
> J2-B01      Description A             02             A              A
Description 05            05            Data 05
> J2-C01      Description A             03             A              A
Description 08            08            Data 08
> J2-A02      Description A             04             A              A
Description 10            10            Data 10
> J2-B02      Description B              05            B               B
Description 07            07            Data 07
> J2-C02      Description B              06            B               B
Description 09            09            Data 09
> J2-A03      Description B              07            B
> J2-B03      Description B              08             B
> J2-C03      Description C              09            C              C
Description 02            02            Data 02
> J2-A04      Description C              10            C              C
Description 06            06            Data 06
> J2-B04      Description D              11            D              D
Description 14            14            Data 14
> J2-C04      Description D              12            D              D
Description 16            16            Data 16
> J2-A05      Description D              13            D
> J2-B05      Description E               14           E               E
Description 03             03           Data 03
> J2-C05      Description E              15            E               E
Description 12            12            Data 12
> J2-A06      Description E              16            E               E
Description 18            18            Data 18
> J2-B06      Description E              17            E
> J2-C06      Description F              18            F               F
Description 01            01            Data 01
> J2-A07      Description F              19            F               F
Description 11            11            Data 11
> J2-B07      Description F              20            F               F
Description 15            15            Data 15
>
> I also realized that the example had too many records in Table2:
> A Description 13 13 Data 13
> C Description 17 17 Data 17
>
> Therefore on Connectors J1 and J3, I can connect wires between the
following Pins:
>
> J1 J3
>
> 01 04
> 02 05
> 03 08
> 04 10
> 05 07
> 06 09
> 07
> 08
> 09 02
> 10 06
> 11 14
> 12 16
> 13
> 14 03
> 15 12
> 16 18
> 17
> 18 01
> 19 11
> 20 15
>
> I certainly appreciate your help and patience.  My formal training in
ACCESS consists of the HELP button and lots of learning by mistakes.  I am
also in the Western US, so the time difference may be apparent.
>
> Thanks,
>
> BG
>
>
>
>
>
Quantcast