asked on

Oracle join

I have table A with sys_ID,business_ID,update_date columns
1 102 11/20/1990
2 102 11/21/2009

it will be join table B business_ID,....C

I only want to get the max(update_date) row to join to B..C
can I do like this?

select * from B,C,(select * from A group by buisiness_ID having update_date=max(update_date) A
where D.business_ID=B.business_ID
and B.ID=C.ID

Is there any way to do this? because if I join many tables and A has many columns
, it is painful

Ravindran Gopinathan

select a.*,b.*
(select sys_id, business_id, max(update_date) from tableA) a,
tableB b
where tableA.business_id=tableB.business_id
and tableA.sys_id=tableB.sys_id

I am assuming you want max of the update_date by grouping the other 2 columns!

SayYou_SayMe

ASKER

select sys_id, business_id, max(update_date) from tableA
have to "group by sys_id, business_id"

SayYou_SayMe

ASKER

CAN these 2 query get the same result?

SELECT A.BUSINESS_ID,A.UPDATE_DATE FROM
( select A.BUSINESS_ID,A.UPDATE_DATE from A,B,C
where A.BUSINESS_ID=B.BUSINESS_ID
and B.ID=C.ID) D
GROUP BY D.BUSINESS_ID
HAVING UPDATE_DATE=MAX(UPDATE_DATE)

select A.BUSINESS_ID,A.UPDATE_DATE from B,C,(select A.BUSINESS_ID,A.UPDATE_DATE from A group by buisiness_ID having update_date=max(update_date) A
where D.business_ID=B.business_ID
and B.ID=C.ID

Sharath S

check this.

SELECT * 
  FROM A t1,B t2,C t3 
 WHERE t1.business_ID = t2.business_ID 
       AND t2.ID = t3.ID 
       AND t1.update_date = (SELECT t4.update_date 
                               FROM A t4 
                              WHERE t1.business_ID = t2.business_ID)

Open in new window

ASKER CERTIFIED SOLUTION

CaliAli

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial