March 11, 2005 at 11:57 am
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.
March 11, 2005 at 12:12 pm
Is there a primary/foreign key relationship between the two tables?
There would need to be a way to join the two tables together and match rows.
March 11, 2005 at 12:20 pm
select
a.RegDate as A_RegDate,
b.RegDate as B_RegDate,
(case When A.RegDate > b.RegDate Then A.RegDate Else B.RegDate end) as Max_RegDate
from
TableA a
Full outer join
TableB b
on a.RegDate = b.RegDate
As long as there are no duplicates in each table you should be fine
HTH
* Noel
March 11, 2005 at 12:22 pm
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
March 11, 2005 at 12:35 pm
Hi noeld,
Thanks for the response..but there is NOT necessary having equal date..it just simply put both column side by side.
March 11, 2005 at 12:50 pm
..it just simply put both column side by side.
To do that then you need to know which row goes with which row as Mark suggested above!
or better yet -- HOW YOU know which row on A goes with which on B?
* Noel
March 11, 2005 at 1:07 pm
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
If the ID values for both tables can be matched as described above, then the following modification
to Noel's query will work.
select
a.RegDate as A_RegDate,
b.RegDate as B_RegDate,
(case When A.RegDate > b.RegDate Then A.RegDate Else B.RegDate end) as Max_RegDate
from
TableA a
Full outer join
TableB b
on a.ID = b.ID
If they can't be matched in this fashion then we're back to square one.
Mark
March 11, 2005 at 1:12 pm
From looking at the exact same topic on the sqlteam forums, the fundamental problem with this is that the poster thinks the tables are "sorted" and doesn't realise that SQL tables are unsorted heaps, no matter what way you put the data into them.
Until that misunderstanding is cleared up, this will stay firmly stuck at square one.
March 11, 2005 at 1:16 pm
Agreed!
* Noel
March 11, 2005 at 1:34 pm
Well..thanks for all of you. Your responses are important and help me very much.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply