Interesting join

I'm looking for some guidance on how to create a join between two tables
given the following...
Table1
ID SeqNum
A 1
A 2
B 1
B 2
B 3
Table2
ID SeqNum
A 33
A 36
B 66
B 67
B 99
The problem here is that Table2's Seqnum field does not contain the same
value as Table1's Seqnum field. However, it is guaranteed to contain the
same sequential ordering within ID's. Therefore, the lowest Seqnum value in
Table1 logically links with the lowest Seqnum value in Table2 for each ID.
For example, given this sequential relationship, the following is the join
we're trying to achieve for ID B:
ID Table1.SeqNum Table2.SeqNum
B 1 66
B 2 67
B 3 99
Is there any way to logically join these two tables given the differing
Seqnum's?
Thanks in advanceSo we are talking about a simple join of the 2 tables on seqnum except that
there is an unknown offset between the actual numbering of seqnum?
Determine (query) what this offset is, store the result in a variable, and
incorporate the offset as needed in the join condition. Of course you know
that this is not a normalized relationship.
declare @.offset as int
select @.offset =
(select max(seqnum) from table2) - (select max(seqnum) from table1)
select
table1.id,
table1.seqnum,
table2.id,
table2.seqnum
from
table1
left join
table2
on table2.seqnum = (table1.seqnum + @.offset )
"Cipher" <ccotrone@.hotmail(remove).com> wrote in message
news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
> I'm looking for some guidance on how to create a join between two tables
> given the following...
> Table1
> ID SeqNum
> A 1
> A 2
> B 1
> B 2
> B 3
>
> Table2
> ID SeqNum
> A 33
> A 36
> B 66
> B 67
> B 99
> The problem here is that Table2's Seqnum field does not contain the same
> value as Table1's Seqnum field. However, it is guaranteed to contain the
> same sequential ordering within ID's. Therefore, the lowest Seqnum value
in
> Table1 logically links with the lowest Seqnum value in Table2 for each ID.
> For example, given this sequential relationship, the following is the join
> we're trying to achieve for ID B:
> ID Table1.SeqNum Table2.SeqNum
> B 1 66
> B 2 67
> B 3 99
> Is there any way to logically join these two tables given the differing
> Seqnum's?
>
> Thanks in advance
>|||Cipher, try,
SELECT ...
FROM T1 AS T1A JOIN T2 AS T2A
ON T1A.id = T2A.id
AND (SELECT COUNT(*) FROM T1 AS T1B
WHERE T1B.id = T1A.id
AND T1B.seqnum <= T1A.seqnum) =
(SELECT COUNT(*) FROM T2 AS T2B
WHERE T2B.id = T2A.id
AND T2B.seqnum <= T2A.seqnum)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Cipher" <ccotrone@.hotmail(remove).com> wrote in message
news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
> I'm looking for some guidance on how to create a join between two tables
> given the following...
> Table1
> ID SeqNum
> A 1
> A 2
> B 1
> B 2
> B 3
>
> Table2
> ID SeqNum
> A 33
> A 36
> B 66
> B 67
> B 99
> The problem here is that Table2's Seqnum field does not contain the same
> value as Table1's Seqnum field. However, it is guaranteed to contain the
> same sequential ordering within ID's. Therefore, the lowest Seqnum value
> in Table1 logically links with the lowest Seqnum value in Table2 for each
> ID. For example, given this sequential relationship, the following is the
> join we're trying to achieve for ID B:
> ID Table1.SeqNum Table2.SeqNum
> B 1 66
> B 2 67
> B 3 99
> Is there any way to logically join these two tables given the differing
> Seqnum's?
>
> Thanks in advance
>|||Assuming the SeqNums in Table1 are indeed sequential, with no gaps:
SELECT t1.ID, t1.SeqNum, t2.SeqNum
FROM Table1 t1
INNER JOIN (
SELECT t3.ID, t3.SeqNum, COUNT(*) AS cnt
FROM Table2 t3
INNER JOIN table2 t4
ON t3.SeqNum <= t4.SeqNum
GROUP BY t3.ID, t3.SeqNum
) t2
ON t1.ID = t2.ID
AND t1.SeqNum = t2.SeqNum
if the SeqNums in Table1 are not sequential, you have to repeat the dose for
Table1 as well:
SELECT t1.ID, t1.SeqNum, t2.SeqNum
FROM (
SELECT t3.ID, t3.SeqNum, COUNT(*) AS cnt
FROM Table1 t3
INNER JOIN table1 t4
ON t3.SeqNum <= t4.SeqNum
GROUP BY t3.ID, t3.SeqNum
) t1
INNER JOIN (
SELECT t5.ID, t5.SeqNum, COUNT(*) AS cnt
FROM Table2 t5
INNER JOIN table2 t6
ON t5.SeqNum <= t6.SeqNum
GROUP BY t5.ID, t5.SeqNum
) t2
ON t1.ID = t2.ID
AND t1.SeqNum = t2.SeqNum
Jacco Schalkwijk
SQL Server MVP
"Cipher" <ccotrone@.hotmail(remove).com> wrote in message
news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
> I'm looking for some guidance on how to create a join between two tables
> given the following...
> Table1
> ID SeqNum
> A 1
> A 2
> B 1
> B 2
> B 3
>
> Table2
> ID SeqNum
> A 33
> A 36
> B 66
> B 67
> B 99
> The problem here is that Table2's Seqnum field does not contain the same
> value as Table1's Seqnum field. However, it is guaranteed to contain the
> same sequential ordering within ID's. Therefore, the lowest Seqnum value
> in Table1 logically links with the lowest Seqnum value in Table2 for each
> ID. For example, given this sequential relationship, the following is the
> join we're trying to achieve for ID B:
> ID Table1.SeqNum Table2.SeqNum
> B 1 66
> B 2 67
> B 3 99
> Is there any way to logically join these two tables given the differing
> Seqnum's?
>
> Thanks in advance
>|||Let's hope he doesn't have 1M rows or this join would occupy his server's
CPU all night long.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uRKh4UiZFHA.2444@.TK2MSFTNGP15.phx.gbl...
> Assuming the SeqNums in Table1 are indeed sequential, with no gaps:
> SELECT t1.ID, t1.SeqNum, t2.SeqNum
> FROM Table1 t1
> INNER JOIN (
> SELECT t3.ID, t3.SeqNum, COUNT(*) AS cnt
> FROM Table2 t3
> INNER JOIN table2 t4
> ON t3.SeqNum <= t4.SeqNum
> GROUP BY t3.ID, t3.SeqNum
> ) t2
> ON t1.ID = t2.ID
> AND t1.SeqNum = t2.SeqNum
> if the SeqNums in Table1 are not sequential, you have to repeat the dose
for
> Table1 as well:
> SELECT t1.ID, t1.SeqNum, t2.SeqNum
> FROM (
> SELECT t3.ID, t3.SeqNum, COUNT(*) AS cnt
> FROM Table1 t3
> INNER JOIN table1 t4
> ON t3.SeqNum <= t4.SeqNum
> GROUP BY t3.ID, t3.SeqNum
> ) t1
> INNER JOIN (
> SELECT t5.ID, t5.SeqNum, COUNT(*) AS cnt
> FROM Table2 t5
> INNER JOIN table2 t6
> ON t5.SeqNum <= t6.SeqNum
> GROUP BY t5.ID, t5.SeqNum
> ) t2
> ON t1.ID = t2.ID
> AND t1.SeqNum = t2.SeqNum
>
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Cipher" <ccotrone@.hotmail(remove).com> wrote in message
> news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
the
value
each
the
>|||Thanks Itzik, this works perfectly.
I was going down the road of using cursors and programmatically replicating
the relationship logic but this has saved me from that pain.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:OkUfSSiZFHA.3984@.TK2MSFTNGP10.phx.gbl...
> Cipher, try,
> SELECT ...
> FROM T1 AS T1A JOIN T2 AS T2A
> ON T1A.id = T2A.id
> AND (SELECT COUNT(*) FROM T1 AS T1B
> WHERE T1B.id = T1A.id
> AND T1B.seqnum <= T1A.seqnum) =
> (SELECT COUNT(*) FROM T2 AS T2B
> WHERE T2B.id = T2A.id
> AND T2B.seqnum <= T2A.seqnum)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Cipher" <ccotrone@.hotmail(remove).com> wrote in message
> news:%23RpJ4GiZFHA.3864@.TK2MSFTNGP10.phx.gbl...
>|||With proper indexing on a million row table, it will take a number of
minutes, but nothing close to all night. But at least it provides the
required result.
Jacco Schalkwijk
SQL Server MVP
"JT" <someone@.microsoft.com> wrote in message
news:uqwKEYiZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Let's hope he doesn't have 1M rows or this join would occupy his server's
> CPU all night long.
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:uRKh4UiZFHA.2444@.TK2MSFTNGP15.phx.gbl...
> for
> the
> value
> each
> the
>