It is necessary that one line was returned only from INNER JOIN

Question

I would like to return the first row only from the inner join. I have two tables:

TABLE_X | TABLE_Y id | id creationdate xid 1 | 1 01/01/2011 1 2 | 2 01/01/2011 1 3 | 3 31/12/2010 2 4 | 4 28/12/2010 3 

Strings in TABLE Y can have identical creation dates, so I get MAX (createdate) first and then MAX (id) from this set, for example:

 SELECT a.id, c.id, d.id, e.id, d.CREATIONDATE, a.REFNUMBER, a.DATECREATED, a.DESCRIPTION, e.CATEGORYCODE, e.OUTSTANDINGAM_MONAMT, e.PREVPAIDAMOUN_MONAMT, e.TOTALINCURRED_MONAMT, e.LOSSFROMDATE, FROM TABLE_A a INNER JOIN TABLE_B b ON (b.id = a.id) INNER JOIN TABLE_C c ON (c.id = b.id) INNER JOIN TABLE_D d ON ( ci = ( select d.id FROM TABLE_D WHERE TABLE_D.id = c.id AND TABLE_D.id = ( select max(id) from TABLE_D t1 where c_id = c.id and CREATIONDATE = ( select max(CREATIONDATE) from TABLE_D t2 where t2.c_id = t1.c_id ) ) ) ) INNER JOIN TABLE_E e ON ( di = ( select e.d_id from TABLE_E where d_id = d.id AND id = ( select max(id) from e t1 where e.d_id = d.id and CREATIONDATE = ( select max(CREATIONDATE) from TABLE_E t2 where t2.d_id = t1.d_id ) ) ) ) 

This works when I call it myself, but when I add it to the INNER JOIN, I get a row for each corresponding row in table Y.

What I want is the last createdate and id entry, where xid = id from TABLE_X.

Answer 1

Try this request

 select *,( select top 1 creationdate from Table_Y where from Table_Y.xId = m.id order by Table_Y.CreationDate ) from Table_X m 

The subquery will select the first result that max creatdate will have, and the main query will display all the records so that you get the desired result.

Answer 2

This should do it. A complex subquery returns the maximum date for each group Y.xid, and from there it will issue Max Y_ID (let this be the key in table Y)

 SELECT X.*, Y.* FROM TABLE_X X INNER JOIN ( SELECT t1.xid, Max(t1.Y_id) MaxY_id FROM (SELECT t2.xid, MAX(t2.CREATIONDATE) MDate FROM TABLE_Y t2 GROUP BY t2.xid) t inner join TABLE_Y t1 on t.xid=t1.xid and t.MDate = t1.CREATIONDATE) MAXY ON MAXY.xid = X.ID INNER JOIN TABLE_Y Y ON Y.Y_ID = MAXY.MAXY_ID 

Answer 3

"when do I add it to the inner join"? what is the inner connection? with which internal connection? The question is poorly stated, but I think you need it (I just use the views to be clear, you can just simply put them in braces and build one big query):

 -- latest pairs of (id,creation) per xid create view latest_id_per_xid as select xid,max(id) id,max(creation) creation from table_y group by xid; -- this view leaves only the rows from table_y that have the same (id,creation,idx) -- as the newest rows identified in the former view (it basically a semijoin) -- you could also join on id alone create view latest_whole_y as select table_y.* from table_y natural join latest_id_per_xid; -- now the answer is easy: select * from table_x join latest_whole_y 

I do not have a database for checking small errors, but it should work fine. (caution: the big assumption is that you never had an entry with a newer identifier and an older date)