question about comparing rows in a table 2

[teach314]

I have a table with about 1000 rows like this:

ID   x0   x1   x2   x3   x4 ...   x9
 1   34   75   19    9   67       10
 2   21   44   79   86    8       22
 3   34   75   19    9   67       10
 4   ETC...

I want to identify values of ID that have the IDENTICAL values of x0 to x9 in the same order.
In the table above, ID 1 and ID 3 meet this condition.
I do this by using a query that uses 2 copies of the table, joined in all 10 'x' fields. So far, so good!

For my own learning of SQL, I want to do the same thing using a NORMALIZED version of table, like this:

ID   Col   X
 1    0    34
 1    1    75
 1    2    19
... 
 2    0    21
 2    1    44
ETC...

I'm sure this is very straightforward, but I'm not getting the SQL to work. Using the normalized table, how do I find ID values that have the same set of 10 x values in the same order.

Thanks in advance for helping this newbie!

 

[BigRed1212]

Does this do it for you?

SQL fiddle

Data:
CREATE TABLE Table1
(`ID` int, `Col` int, `X` int)
;

INSERT INTO Table1
(`ID`, `Col`, `X`)
VALUES
(1, 0, 34),
(1, 1, 75),
(1, 2, 19),
(2, 0, 21),
(2, 1, 44),
(2, 2, 79),
(3, 0, 34),
(3, 1, 75),
(3, 2, 19)
;

SQL:
select a.id
from table1 a inner join table1 b
on a.id<>b.id and a.col=b.col and a.x=b.x
group by a.id
order by a.id

Result:
ID
1
3

 

[teach314]

Thank you for responding, but I think this just shows that there is at least 1 match between the x values of ID 1 and the x values of ID 3.

I need to find PAIRS of ID values that have exactly 10 MATCHES between both their 'Col' values and their 'x' values. In other word, which pairs of ID values have exactly the same 10 'x' values in exactly the same order?

 

[dhookom]

The easiest solution might be to create a crosstab query based on your normalized data so it looks exactly like your un-normalized data. Then use "a query that uses 2 copies of the table crosstab query, joined in all 10 'x' fields"

Duane
Hook'D on Access
MS Access MVP

 

[BigRed1212]

I see now.

Revised data:

VALUES
(1, 0, 34),
(1, 1, 75),
(1, 2, 19),
(2, 0, 21),
(2, 1, 44),
(2, 2, 79),
(3, 0, 34),
(3, 1, 75),
(3, 2, 20),
(4, 0, 34),
(4, 1, 75),
(4, 2, 19)
;

1 and 3 will have 2 matches, while 1 and 4 will have all 3 matches.

Revised SQL:

select a.id, a.x
from table1 a inner join table1 b
on a.id<>b.id and a.col=b.col and a.x=b.x
group by a.id, a.x
order by a.id

Results:
ID X
1 19
1 34
1 75
3 34
3 75
4 19
4 34
4 75

Shows us the ones with 2 and 3 matches.

Now we should be able to select from that:

select b.id
from
(
select a.id, a.x
from table1 a inner join table1 b
on a.id<>b.id and a.col=b.col and a.x=b.x
group by a.id, a.x
) b
group by b.id having count(*) >2
order by b.id

 

[BigRed1212]

Didn't keep my aliases straight! It will still work, but I would change the outer alias to c as I already had a b in the subquery.

 

[teach314]

TQ to BigRed1212 and DHookum for your time. 2 comments...

1) BigRed1212 - this works well, but still has a weakness. Let's say ID 1, 3, and 5 have the same x values in the same order, and also ID 2 and 4 have the same x values in the same order. The output shows that all 5 of these IDs are involved in matches, but isn't clear as to which IDs are matched to which other IDs.

2) DHookum - this works well, and is fast. As mentioned in my original posting, I had already used a solution like this starting with a non-normalized table, but was looking for a solution based on a normalized table. (This comes from endless warnings in this forum to normalize!!) It could be that in cases like this, non-normalization is the way to go. Any thoughts?

thanks again to both responders

 

[dhookom]

Normalize, normalize, normalize. If the crosstab solution works then go with it. It's pure SQL and involves no code.

Duane
Hook'D on Access
MS Access MVP

 

[BigRed1212]

The output shows that all 5 of these IDs are involved in matches, but isn't clear as to which IDs are matched to which other IDs."

So what you want to know is not just that records are matched but that that 1, 3, and 5 are matched to each other, that 2 and 4 are matched to each other, and that 378 matches 879?

In other words, all the records that belong to each set where there are 10 exact matches?

Before I do much of anything else, let's settle on the exact requirements.

 

[teach314]

hello BigRed1212

"In other words, all the records that belong to each set where there are 10 exact matches". Yes, that's exactly it. I had tried to convey this when I stated, in my original posting, "I want to identify values of ID that have the IDENTICAL values of x0 to x9 in the same order". I probably should have given some sample results to make this clear.

In any case, I'm grateful for the code you have already sent, which comes very close to this goal.

thanks, teach314

 

[BigRed1212]

Okey-dokey.

Modified data:

CREATE TABLE Table1
(`ID` int, `Col` int, `X` int)
;

INSERT INTO Table1
(`ID`, `Col`, `X`)
VALUES
(1, 0, 34),
(1, 1, 75),
(1, 2, 19),
(2, 0, 21),
(2, 1, 44),
(2, 2, 79),
(3, 0, 34),
(3, 1, 75),
(3, 2, 20),
(4, 0, 34),
(4, 1, 75),
(4, 2, 19),
(5, 0, 21),
(5, 1, 44),
(5, 2, 79),
(6, 0, 34),
(6, 1, 75),
(6, 2, 19)
;

For this data, 1 equals 4 and 6, 2 equals 5, and nobody likes 3.

SQL like:

SELECT f.cdot, f.ddot
FROM
(
select c.id as cdot, d.id as ddot
from
(
select a.id, a.x
from table1 a inner join table1 b
on a.id<>b.id and a.col=b.col and a.x=b.x
group by a.id, a.x
order by a.id
) c
inner join
(
select a.id, a.x
from table1 a inner join table1 b
on a.id<>b.id and a.col=b.col and a.x=b.x
group by a.id, a.x
order by a.id
) d
on c.id <> d.id and c.x=d.x
) f
group by f.cdot, f.ddot
having count(*) > 2
order by f.cdot

gives us results of:
CDOT DDOT
1 4
1 6
2 5
4 1
4 6
5 2
6 1
6 4

showing 1 matches 4 and 6 and that 2 matches 5. I'm not good enough to consolidate those down and make 1-4 and 4-1 only show as one match, but maybe this way is better/more useful in that you can look up anything in the left hand column and see what it matches in the right hand column.

For 10 matches I think the having count number goes to 9.

I think this query will be v-e-r-y v-e-r-y slow, but I might be wrong.

 

[teach314]

awesome!