May 28, 2007 at 10:24 pm
I am fairly new at writing SQL.
Scenario.
Table A - all fields in select
Table B- one field in select
Join fields Z_Id and DateCode
But
Table B has less Z_ID and datecode records then Table A
e.g Table A has 1234a, 20070123
but
Table B has 1234a, 20070122
and 1234a, 20070124 (20070123 is missing)
I want to join on
matching Z_id and DateCode
OR
take the record from Table B that has the
greater date. i.e in above example 1234a,20070124
to join to Table A 1234a, 20070123.
Hmmmm need some expert advice.......
May 28, 2007 at 10:52 pm
Try
SELECT *
FROM TableA A
INNER JOIN TableB B ON B.Z_Id = A.Z_Id
AND B.Date = (SELECT MIN(Z.Date) FROM TableB Z WHERE B.Date > A.Date)
K. Matsumura
May 28, 2007 at 10:54 pm
Correction
SELECT *
FROM TableA A
INNER JOIN TableB B ON B.Z_Id = A.Z_Id
AND B.Date = (SELECT MIN(Z.Date) FROM TableB Z WHERE B.Date >= A.Date)
K. Matsumura
May 28, 2007 at 10:55 pm
Another Correction
SELECT *
FROM TableA A
INNER JOIN TableB B ON B.Z_Id = A.Z_Id
AND B.Date = (SELECT MIN(Z.Date) FROM TableB Z WHERE Z.Z_Id = A.Z_Id AND Z.Date >= A.Date)
K. Matsumura
May 28, 2007 at 11:38 pm
Thanks very much I will try and let you know how I go...
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply