Re: SQL problem - join

["Arosemena, Efrain" <Earosem@TOYOTARP.COM>]

Howdy !

Sorry for my english...
i think that you get it using this query:

BEGIN-SELECT
'Key 1'
FIELD_A
max(DATE_A)
from TABLE
where
field_a= 'field_a'
and @@rowcount() = 1
union
select
'Key 2',
FIELD_B
DATE_B
from TABLE
where
field_b= 'field_b'
and @@rowcount() = 1
order by 1
END-SELECT

-----Mensaje original-----
De: C. Duncan Hudson [mailto:c_duncan_hudson@YAHOO.COM]
Enviado el: Monday, March 13, 2000 3:03 PM
Para: SQR-USERS@list.iex.net
Asunto: SQL problem - join


Hi, I have an SQL problem that I need help with.  I
have two tables w/ a common key, but not necessarily
the same number of records.  There is a date field in
each table.  I want to run a select statement to get
the key, several fields, and the most recent date from
each table.  I really need to do it, if possible, with
a single SQL statement.  Can anyone get me started?
Thanks in advance.


Dunc.


PS.  Here's a visual of what I'm talking about:


Table A
KEY     FIELD     DATE
key_1   xx        2/12/98
key_1   x         2/13/99
key_1   x         3/15/98
key_2   y         3/16/98
key_3   z         3/18/99
key_3   zz        1/1/00


Table B
KEY     FIELD     DATE
key_1   qq        3/19/97
key_1   q         2/36/67
key_2   r         3/11/15
key_2   rr        3/7/75
key_3   ss        1/12/99


Result
KEY    FIELD(a)  FIELD(b)  DATE(a)  DATE(b)
key_1  x         qq        2/13/99  3/19/97
key_2  y         rr        3/16/98  3/7/75
key_3  zz        ss        1/1/00   1/12/99


__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com