Joining a Table to itself - 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

General SQL Server Forums

New to SQL Server Programming

Joining a Table to itself

Author

Topic

A.J.Gibson
Starting Member

9 Posts

Posted - 2009-06-30 : 17:27:23

I'm trying to figure out how to join a table to itself with some very weird criteria, and I would appreciate any insight into how it could be done.

Basically, each row in the table can be of one of two possible types and has a timestamp, and I need to create a resulting table such that each member of the first type is joined to the member of second type that occurs after it chronologically after it, and only that one instance. If the first type has an extra hanging off the end, it should be given a null for it's second time (he assumption being the second thing hasn't happened yet). We can assume that data is going to be generally pairable in this way (there can't be a second X until there has been a Y), with the exception being the afford mentioned first type with second type having not happened yet. There may be a junk second type at the beginning, which can be ignored.

Confused? Here is my example:

Before:

Type: Time:
Y 0
X 3
Y 7
X 8
Y 9
X 11
Y 17
X 20

After:

Type1: Time1: Type2: Time2:
X 3 Y 7
X 8 Y 9
X 11 Y 17
X 20 Null Null

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-30 : 17:36:39

No need for self-joining.

DECLARE	@Sample TABLE
	(
		typ CHAR(1),
		tim INT
	)

INSERT	@Sample
SELECT	'Y',  0 UNION ALL
SELECT	'X',  3 UNION ALL
SELECT	'Y',  7 UNION ALL
SELECT	'X',  8 UNION ALL
SELECT	'Y',  9 UNION ALL
SELECT	'X', 11 UNION ALL
SELECT	'Y', 17 UNION ALL
SELECT	'X', 20

SELECT		MAX(CASE WHEN recID % 2 = 0 THEN typ ELSE NULL END) AS typ1,
		MAX(CASE WHEN recID % 2 = 0 THEN tim ELSE NULL END) AS tim1,
		MAX(CASE WHEN recID % 2 = 1 THEN typ ELSE NULL END) AS typ2,
		MAX(CASE WHEN recID % 2 = 1 THEN tim ELSE NULL END) AS tim2
FROM		(
			SELECT	typ,
				tim,
				ROW_NUMBER() OVER (ORDER BY tim) - 1 AS recID
			FROM	@Sample
			WHERE	tim > 0
		) AS d
GROUP BY	recID / 2
ORDER BY	recID / 2

N 56°04'39.26"
E 12°55'05.63"

GrTech2009
Starting Member

6 Posts

Posted - 2009-06-30 : 18:20:51

What does UNION ALL mean? Also what role does % play in the following code snippet.

SELECT MAX(CASE WHEN recID % 2 = 0 THEN typ ELSE NULL END) AS typ1,
MAX(CASE WHEN recID % 2 = 0 THEN tim ELSE NULL END) AS tim1,
MAX(CASE WHEN recID % 2 = 1 THEN typ ELSE NULL END) AS typ2,
MAX(CASE WHEN recID % 2 = 1 THEN tim ELSE NULL END) AS tim2

I always used it like this. SELECT * FROM Name WHERE FIRSTNAME LIKE '%Geo'

Please explain thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-01 : 02:58:07

% means MODULO.

The first part is only to setup a test environment.
You should concentrate on the last statement, the SELECT query that starts with MAX(...

N 56°04'39.26"
E 12°55'05.63"

A.J.Gibson
Starting Member

9 Posts

Posted - 2009-07-02 : 10:34:44

Peso!
Thanks for you're insight, you've definitely given me something to think about. The thing is: the actual table doesn't neccesarily alternate X/Y/X/Y because it has more than one object on it; so it's more like X1/X2/X3/Y2/X4/Y3/X2/Y1 with the object identified by another field; the example was a simplification.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 11:20:42

so in that case you always want a X value with next followed Y value?

A.J.Gibson
Starting Member

9 Posts

Posted - 2009-07-02 : 11:38:37

Let me do a more complicated example:
Type Time Name
X 03 Device1
X 04 Thingy2
X 07 Gizmo3
Y 09 Thingy2
X 12 Dohicky4
Y 13 Device1
X 15 Thingy2
X 16 Device1
Y 20 Device1
Y 23 Gizmo3
Y 27 Dohicky4

Would result in:
Start StartTime Name End EndTime
X 03 Device1 Y 13 Device1
X 04 Thingy2 Y 09 Thingy2
X 07 Gizmo3 Y 23 Gizmo3
X 12 Dohicky4 Y 27 Dohicky4
X 15 Thingy2 NULL 0
X 16 Device1 Y 20 Device1

Each X event is matched to the next Y event that has the same name, with an unfinished pair getting a null/0 instead

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 11:53:16

ok. try this


SELECT t.Type, t.Time, t.Name,t1.Type, COALESCE(t1.Time,0), t1.Name
FROM YourTable t
OUTER APPLY (SELECT TOP 1 Type, Time, Name
             FROM YourTable
             WHERE Time > t.Time
             AND Type ='Y'
             AND Name = t.Name
             ORDER BY Time ASC) t1
WHERE t.Type='X'

A.J.Gibson
Starting Member

9 Posts

Posted - 2009-07-02 : 12:19:01

Is there any way I can not use Outer Apply? I'm not using SQL Server for this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-02 : 12:29:21

SELECT 'x',
x.time,
'y',
y.time
FROM (SELECT Type, Time, ROW_NUMBER() OVER (ORDER BY time) as recid FROM Table1 WHERE Type = 'X') AS x
FULL JOIN (SELECT Type, Time, ROW_NUMBER() OVER (ORDER BY time) as recid FROM Table1 WHERE Type = 'Y') AS y on y.recid = x.recid
order by coalesce(x.recid, y.recid)

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 12:29:59

quote:
Originally posted by A.J.Gibson

Is there any way I can not use Outer Apply? I'm not using SQL Server for this.

then why posting in sql server forum? post it in relevant forum

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-02 : 12:31:43

quote:
Originally posted by Peso

SELECT 'x',
x.time,
'y',
y.time
FROM (SELECT Type, Time, ROW_NUMBER() OVER (ORDER BY time) as recid FROM Table1 WHERE Type = 'X') AS x
FULL JOIN (SELECT Type, Time, ROW_NUMBER() OVER (ORDER BY time) as recid FROM Table1 WHERE Type = 'Y') AS y on y.recid = x.recid
order by coalesce(x.recid, y.recid)

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

Dont you need to link by Name values also?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-02 : 12:35:30

Probably. I didn't read the last specification well.

Add "PARTITION BY Name" before the "ORDER BY time".
Add "AND y.Name = x.Name" to the JOIN clause.
Add ", Name" to the both inner select lists.
Add ", x.name" and ", y.name" to outer select list.

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources