Join column - 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 2000 Forums

SQL Server Development (2000)

Join column

Author

Topic

agungsh
Starting Member

3 Posts

Posted - 2005-03-11 : 13:36:39

I have two tables :
Table A :

RegDate
2005/1/15
2005/1/16
2005/1/18
2005/1/20

Table B :

RegDate
2005/1/15
2005/1/17
2005/1/18
2005/1/19
2005/1/20
2005/1/22

I want to display :

A_RegDate B_RegDate MaxDate
2005/1/15 2005/1/15 2005/1/15
2005/1/16 2005/1/17 2005/1/17
2005/1/18 2005/1/18 2005/1/18
2005/1/20 2005/1/19 2005/1/20
NULL 2005/1/20 NULL
NULL 2005/1/22 NULL

Note:
*MaxDate is the highest date between A_regdate and B_RegDate in the current row.
*The row count of both tables may vary.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-11 : 13:47:23

Surely you must have other columns in these table besides a single RegDate column?!? Something to releate one table to the other?

Be One with the Optimizer
TG

agungsh
Starting Member

3 Posts

Posted - 2005-03-11 : 14:26:22

quote:
Originally posted by TG

Surely you must have other columns in these table besides a single RegDate column?!? Something to releate one table to the other?

Be One with the Optimizer
TG

yes..it can have a primary key for each table..but there is NO direct relation between both primary keys.

Let's say :

Table A :
ID RegDate
1 2005/1/15
2 2005/1/16
3 2005/1/18
4 2005/1/20

Table B :

ID RegDate
1 2005/1/15
2 2005/1/17
3 2005/1/18
4 2005/1/19
5 2005/1/20
6 2005/1/22

Note:
It just simply put both column side by side.

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-11 : 14:31:32

The order of rows in a database have no meaning...and the relationship between rows based on that offset is meaningless...

Why are you doing this?

Brett

8-)

agungsh
Starting Member

3 Posts

Posted - 2005-03-11 : 14:38:56

quote:
Originally posted by X002548

The order of rows in a database have no meaning...and the relationship between rows based on that offset is meaningless...

Why are you doing this?

Table A and B are sorted by RegDate column (ascending).
And I want to know which rows do not have match.

Hippi
Yak Posting Veteran

63 Posts

Posted - 2005-03-11 : 19:09:12

quote:
Originally posted by agungsh

quote:
Originally posted by X002548

The order of rows in a database have no meaning...and the relationship between rows based on that offset is meaningless...

Why are you doing this?

Table A and B are sorted by RegDate column (ascending).
And I want to know which rows do not have match.

I think u should create a temporary table, insert the date data of the two table to it, then use case to get the max date.

HTH

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-11 : 19:19:06

Not sure why you'd want to do this, but a FULL OUTER JOIN with temp tables will do the trick:


SET NOCOUNT ON

DECLARE @TableA table (RegDate datetime)
DECLARE @TableB table (RegDate datetime)

INSERT INTO @TableA VALUES('2005/1/15')
INSERT INTO @TableA VALUES('2005/1/16')
INSERT INTO @TableA VALUES('2005/1/18')
INSERT INTO @TableA VALUES('2005/1/20')

INSERT INTO @TableB VALUES('2005/1/15')
INSERT INTO @TableB VALUES('2005/1/17')
INSERT INTO @TableB VALUES('2005/1/18')
INSERT INTO @TableB VALUES('2005/1/19')
INSERT INTO @TableB VALUES('2005/1/20')
INSERT INTO @TableB VALUES('2005/1/22')

SELECT IDENTITY(int, 1, 1) AS [ID], RegDate
INTO #TempA
FROM @TableA

SELECT IDENTITY(int, 1, 1) AS [ID], RegDate
INTO #TempB
FROM @TableB

SELECT 	
	a.RegDate AS A_RegDate, 
	b.RegDate AS B_RegDate, 
	MaxDate = 
		CASE 
			WHEN a.RegDate > b.RegDate THEN a.RegDate
			WHEN b.RegDate > a.RegDate THEN b.RegDate
			ELSE a.RegDate
		END
FROM #TempA a
FULL OUTER JOIN #TempB b
ON a.[ID] = b.[ID]

DROP TABLE #TempA
DROP TABLE #TempB

Tara

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-11 : 19:59:38

This will give you the unmatched dates and return only the dates that do not have matches. Grouping eliminates possible duplicates.

select
	a.RegDate,
	IN_A = max(IN_A),	-- 1 if in A, else 0
	IN_B = max(IN_B)	-- 1 if in B, else 0
from
	(
	select RegDate, IN_A=1, IN_B=0 from TABLE_A group by RegDate 
	union all
	select RegDate, IN_A=0, IN_B=1 from TABLE_B group by RegDate
	) a
group by
	a.RegDate
having
	Count(*) <> 2
order by
	a.RegDate

quote:
Originally posted by agungsh
Table A and B are sorted by RegDate column (ascending).
And I want to know which rows do not have match.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources