Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Need one row only returned from INNER JOIN

I would like to return the first row only from an 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

Rows in TABLE Y can have identical creation dates so I am first getting the MAX(creationdate) and then 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
(
   c.i =
   (
      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
(
   d.i =
   (
      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 on it's own but when I add it to an INNER JOIN I am getting a row for each matching row in table Y.

What I want is the latest record by creationdate and id where xid = id from TABLE_X.

like image 864
adamdunne Avatar asked Jan 17 '11 17:01

adamdunne


2 Answers

Try this query

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 sub query will pick the top 1 result which have max creationdate and the main query will pick all the records so you have your desired result

like image 178
Fraz Sundal Avatar answered Oct 24 '22 23:10

Fraz Sundal


This should do it The complex subquery works out the max date for each Y.xid group, and from that, further works out the Max Y_ID (let this represent the key on 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
like image 24
RichardTheKiwi Avatar answered Oct 24 '22 22:10

RichardTheKiwi