Self Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Self Join

Author

Topic

cable
Starting Member

3 Posts

Posted - 2008-08-12 : 02:17:26

Hi All,

I have 2 tables; one which is a list of Groups

+---------+-------+
|MemberID | Group |
+---------+-------+
|Fred |a |
|Fred |b |
|Sue |a |
|Jack |b |
|Jack |c |
+---------+-------+

And a table of Conflicts

+---------+-------+-------+-------+
|Conflict |Group1 |Group2 |Group3 |
+---------+-------+-------+-------+
|1 |a | | |
|2 |a |b | |
|3 |a |b |c |
|4 |b |c | |
|5 |c |d |e |
+---------+-------+-------+-------+

I'm trying to obtain a list of all members which have a match with a row in Conflicts such that Fred has conflict 1 and 2 (not 3), Sue conflict 1, and Jack conflict 4.

What I've done is joined the Conflicts table to 3 copies of the Groups table:

SELECT g1.MemberID, c1.Conflict, g1.Group1, g2.Group2, g3.Group3
FROM Conflicts c1 INNER JOIN Groups g1 on c1.Group = g1.group1
INNER JOIN Groups g2 on c1.Group = g2.group2 AND g1.MemberID = g2.MemberID
INNER JOIN Groups g3 on c1.Group = g3.group3 AND g2.MemberID = g3.MemberID

But I don't think this is right as I only get results where all 3 columns match and it's slow, I'm not sure i'm on the right track and any help would be much apreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-12 : 03:57:32

Why is Fred not included in conflict 3? we have a in conflict 3 also? How do we distinguish if a corresponds to SUe of Fred in conflicts table?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 04:03:08

Maybe an ALL match only situation?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 04:08:56

And why is one table denormalized and the other normalized?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 04:39:12

[code]DECLARE @Groups TABLE
(
MemberID VARCHAR(4),
[Group] CHAR(1)
)

INSERT @Groups
SELECT 'Fred', 'a' UNION ALL
SELECT 'Fred', 'b' UNION ALL
SELECT 'Sue', 'a' UNION ALL
SELECT 'Jack', 'b' UNION ALL
SELECT 'Jack', 'c'

DECLARE @Conflicts TABLE
(
Conflict INT,
Group1 CHAR(1),
Group2 CHAR(1),
Group3 CHAR(1)
)

INSERT @Conflicts
SELECT 1, 'a', NULL, NULL UNION ALL
SELECT 2, 'a', 'b', NULL UNION ALL
SELECT 3, 'a', 'b', 'c' UNION ALL
SELECT 4, 'b', 'c', NULL UNION ALL
SELECT 5, 'c', 'd', 'e'

;WITH Yak (Conflict, [Group])
AS (
SELECT u.Conflict,
u.[Group]
FROM @Conflicts AS c
UNPIVOT (
[Group]
FOR x IN (c.[Group1], c.[Group2], c.[Group3])
) AS u
), Peso (Conflict, Groups)
AS (
SELECT c.Conflict,
f.r
FROM @Conflicts AS c
CROSS APPLY (
SELECT DISTINCT '' + y.[Group]
FROM Yak AS y
WHERE y.Conflict = c.Conflict
ORDER BY '' + y.[Group]
FOR XML PATH('')
) AS f(r)
), SQLTeam (MemberID, Groups)
AS (
SELECT c.MemberID,
f.r
FROM (
SELECT DISTINCT MemberID
FROM @Groups
) AS c
CROSS APPLY (
SELECT DISTINCT '' + y.[Group]
FROM @Groups AS y
WHERE y.MemberID = c.MemberID
ORDER BY '' + y.[Group]
FOR XML PATH('')
) AS f(r)
)

SELECT s.MemberID,
p.Conflict
FROM Peso AS p
INNER JOIN SQLTeam AS s ON s.Groups = p.Groups[/code]

E 12°55'05.25"
N 56°04'39.16"

cable
Starting Member

3 Posts

Posted - 2008-08-12 : 16:53:49

Thanks Peso, that's quite complicated and I admit having a little difficulty following it. Could you walk me through what you're doing there?

It does have to match all, for fred, who is a member of a and b, matches a conflict (if it exists) in a, b, or ab (or ba). I need to match all conflicts not just the largest fit, when I run your solution I don't get Freds match with conflict 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 17:02:09

[code]SELECT s.MemberID,
p.Conflict
FROM Peso AS p
INNER JOIN SQLTeam AS s ON s.Groups LIKE '%' + p.Groups + '%'
ORDER BY s.MemberID,
p.Conflict[/code]

E 12°55'05.25"
N 56°04'39.16"

cable
Starting Member

3 Posts

Posted - 2008-08-13 : 00:15:10

That works, awesome thanks. Still don't understand it though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources