sql server programming: matching sets, one to many relationship -- sql server programming

Re: matching sets, one to many relationship

Steve Kass
6/13/2004 2:49:41 AM

John,

If numbers entered before or after the combination are ignored, the
Pos values of the Attempt and Combination sequences might not match up.
If the question is what I think it is, maybe something like this will work:

select
CombinationID,
AttemptID
from (
select
Combination.CombinationID,
Attempt.AttemptID,
Attempt.pos as AttemptStart
from Attempt join Combination
on Attempt.val = Combination.val
and Combination.pos = 1
) MatchStart
where not exists (
select *
from Combination
where Combination.CombinationID = MatchStart.CombinationID
and Combination.pos > 1
and Combination.val <> coalesce((
select Attempt.val
from Attempt
where Attempt.AttemptID = MatchStart.AttemptID
and Attempt.pos = Combination.pos + (MatchStart.AttemptStart - 1)
),-1)
)

Try the following, where I've added count(*) to the select list of your
query. You miss one solution, and you get the one you get for the wrong
reason, I think.

CREATE table Attempt ( -- combination attempts
AttemptID int,
pos int,
val int
)

CREATE table Combination ( -- valid combinations, order matters
CombinationID int,
Pos int,
Val int
)

insert into Combination VALUES(1,1,1) -- these are valid combinations,
so we need them to match 1,2,3,4 or 5,6,7 or 8,9
insert into Combination VALUES(1,2,2) -- if they match more than that
its fine as long as the min requirement is met
insert into Combination VALUES(1,3,3)
insert into Combination VALUES(1,4,4)
insert into Combination VALUES(2,1,5)
insert into Combination VALUES(2,2,6)
insert into Combination VALUES(2,3,7)
insert into Combination VALUES(3,1,7)
insert into Combination VALUES(3,2,9)

insert into Attempt VALUES(1,11,4) -- this should fail, correct numbers
but wrong order
insert into Attempt VALUES(1,12,3)
insert into Attempt VALUES(1,13,2)
insert into Attempt VALUES(1,14,1)
insert into Attempt VALUES(1,15,2)
insert into Attempt VALUES(1,16,3)
insert into Attempt VALUES(1,17,4)
insert into Attempt VALUES(2,1,7) -- but here we match cos same numbers
and order
insert into Attempt VALUES(2,2,7)
insert into Attempt VALUES(2,3,9)
go

-- John's query
SELECT Combination.CombinationID, Attempt.AttemptID, COUNT(*)
FROM Combination
LEFT OUTER JOIN
Attempt
ON Combination.Pos = Attempt.Pos AND
Combination.Val = Attempt.Val
GROUP BY Combination.CombinationID, Attempt.AttemptID
HAVING COUNT(*) = COUNT(Attempt.AttemptID)
go

-- Steve Kass
-- Drew University
-- Ref: E17F13C4-BA16-46B9-A7D1-069F57CE7529

[quoted text, click to view]

Re: matching sets, one to many relationship

John Gilson
6/14/2004 5:09:47 AM

[quoted text, click to view]

Thanks Steve for the characteristic cogency. One might also try:

CREATE TABLE Combinations
(
CombinationID INT NOT NULL,
Pos INT NOT NULL,
Val INT NOT NULL,
PRIMARY KEY (CombinationID, Pos)
)

INSERT INTO Combinations VALUES(1,1,1)
INSERT INTO Combinations VALUES(1,2,2)
INSERT INTO Combinations VALUES(1,3,3)
INSERT INTO Combinations VALUES(1,4,4)
INSERT INTO Combinations VALUES(2,1,5)
INSERT INTO Combinations VALUES(2,2,6)
INSERT INTO Combinations VALUES(2,3,7)
INSERT INTO Combinations VALUES(3,1,7)
INSERT INTO Combinations VALUES(3,2,9)

CREATE TABLE Attempts
(
AttemptID INT NOT NULL,
Pos INT NOT NULL,
Val INT NOT NULL,
PRIMARY KEY (AttemptID, Pos)
)

INSERT INTO Attempts VALUES(1,11,4)
INSERT INTO Attempts VALUES(1,12,3)
INSERT INTO Attempts VALUES(1,13,2)
INSERT INTO Attempts VALUES(1,14,1)
INSERT INTO Attempts VALUES(1,15,2)
INSERT INTO Attempts VALUES(1,16,3)
INSERT INTO Attempts VALUES(1,17,4)
INSERT INTO Attempts VALUES(2,1,7)
INSERT INTO Attempts VALUES(2,2,7)
INSERT INTO Attempts VALUES(2,3,9)

SELECT DISTINCT Combinations.CombinationID, Attempts.AttemptID
FROM (SELECT Combinations.CombinationID, Attempts.AttemptID, Attempts.Pos
FROM Attempts
INNER JOIN
Combinations
ON Combinations.Pos = 1 AND
Attempts.Val = Combinations.Val) AS FirstMatches
INNER JOIN
Combinations
ON FirstMatches.CombinationID = Combinations.CombinationID
INNER JOIN
Attempts
ON FirstMatches.AttemptID = Attempts.AttemptID AND
Attempts.Pos = FirstMatches.Pos + Combinations.Pos - 1 AND
Attempts.Val = Combinations.Val
INNER JOIN
(SELECT CombinationID, COUNT(*) AS Tally
FROM Combinations
GROUP BY CombinationID) AS C
ON C.CombinationID = Combinations.CombinationID
GROUP BY Combinations.CombinationID, Attempts.AttemptID, FirstMatches.Pos,
C.Tally
HAVING COUNT(*) = C.Tally

CombinationID AttemptID
1 1
3 2

--
JAG

Re: matching sets, one to many relationship

lloyd
6/14/2004 10:02:33 PM

thanks for the replies, that really helped.

are there any sources (books, sites whatever) you would recommend for
advancing my sql knowledge past simple understanding of keys, joins sp's to
this level? i think i can guess at least one book...

Lloyd Christopher
SLOW30

[quoted text, click to view]

Re: matching sets, one to many relationship

Joe Celko
6/15/2004 8:57:19 AM

Well, I like SQL FOR SMARTIES myself.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.