March 21, 2017 at 12:24 pm
Hi all,
I am using the regular inner join to itself to discover any clashes in a table based on time and date. In this case it is volunteers attending sessions within the same event.
INSERT INTO #SESSIONS
SELECT
'BOB',1,'10/04/2017', '17:00'
UNION ALL
SELECT
'BOB',2,'10/04/2017', '17:00'
UNION ALL
SELECT
'BOB',3,'11/04/2017', '17:00'
UNION ALL
SELECT
'BOB',4,'12/04/2017', '17:00'
UNION ALL
SELECT
'JANE',1,'10/04/2017', '17:00'
UNION ALL
SELECT
'JANE',2,'10/04/2017', '17:00'
UNION ALL
SELECT
'JANE',3,'11/04/2017', '17:00'
SELECT * FROM #SESSIONS
SELECT DISTINCT T1.FIRSTNAME, T1.SESSIONID, T2.SESSIONID
FROM #SESSIONS t1 INNER JOIN
#SESSIONS t2
ON t1.SESSIONDATE = t2.SESSIONDATE
AND t1.SESSIONTIME = t2.SESSIONTIME
AND T1.FIRSTNAME = T2.FIRSTNAME
AND NOT T1.SESSIONID = T2.SESSIONID
ORDER BY FIRSTNAME
DROP TABLE #SESSIONS
This results in a cross duplication so if Bob is on session 1 and session 2 that clash the results will bring up both session 1 and session 2 twice in the opposite columns.
I want to know the session pairings that clash but I dont want to know them in both mirrored pairs. I could remove this in the code but I am sure there is an SQL way =)
Thanks for any help.
March 21, 2017 at 12:35 pm
No create statement for the #Sessions table.
March 21, 2017 at 12:36 pm
Isn't it simply this?
and t1.SessionID < t2.SessionID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 21, 2017 at 12:48 pm
Sean Lange - Tuesday, March 21, 2017 12:36 PMIsn't it simply this?
and t1.SessionID < t2.SessionID
Doh! hand to head..thanks for pointing out the obvious..one pair will always have a lower ID in the first column than the second.
March 21, 2017 at 12:52 pm
kangarolf - Tuesday, March 21, 2017 12:48 PMSean Lange - Tuesday, March 21, 2017 12:36 PMIsn't it simply this?
and t1.SessionID < t2.SessionIDDoh! hand to head..thanks for pointing out the obvious..one pair will always have a lower ID in the first column than the second.
What happens if you have more than 2 that clashing?
March 22, 2017 at 7:59 am
ZZartin - Tuesday, March 21, 2017 12:52 PMkangarolf - Tuesday, March 21, 2017 12:48 PMSean Lange - Tuesday, March 21, 2017 12:36 PMIsn't it simply this?
and t1.SessionID < t2.SessionIDDoh! hand to head..thanks for pointing out the obvious..one pair will always have a lower ID in the first column than the second.
What happens if you have more than 2 that clashing?
Shouldnt matter because in any clash the clash is repeated twice but in only one instance is t1 < t2. If you were comparing three tables you would have issues.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply