SQL Query - Subqueries On Joins

Intro

I have a complex query I need to write (for an Oracle DB). I've tried to make an analogy to avoid using company data, so let me know if something seems weird.

Tables

CAT

• CAT_ID (PK)

KITTEN

• KITTEN_ID (PK)

CAT_KITTEN_PLAY

• PLAY_ID (PK)
• CAT_ID (FK to CAT table)
• KITTEN_ID (FK to KITTEN table)
• KITTEN_GENDER (String - M or F)
• PLAY_TIME (Date)

KITTEN_INFO

• KITTEN_ID (FK to KITTEN table)
• NAME (String, e.g. "Name")
• VALUE (String, e.g. "Duchess")

Description

So basically, the CAT and KITTEN tables just hold IDs and some other random information. KITTEN_INFO holds the name of the kittens (among other things). And CAT_KITTEN_PLAY logs all of the times a cat plays with a kitten.

What I need, is to write a query that returns all of the CATs whose most recent play time with a male KITTEN was in a certain date range. Let's call it Jan 1 2011 to Jan 31 2011. I also need to include the play date and name of the most recent male and female kitten the cat played with.

So Far

Below is what I have so far:

SELE开发者_C百科CT cat.*, lastMale.PLAY_TIME maleTime, lastFemale.PLAY_TIME femaleTime, maleName.VALUE male, femaleName.VALUE female
FROM CAT cat
LEFT JOIN CAT_KITTEN_PLAY lastMale ON lastMale.CAT_ID = cat.CAT_ID 
                                  AND lastMale.GENDER = 'M'
LEFT JOIN CAT_KITTEN_PLAY lastFemale ON lastFemale.CAT_ID = cat.CAT_ID 
                                    AND lastFemale.GENDER = 'F'
LEFT JOIN KITTEN_INFO femaleName ON femaleName.KITTEN_ID = lastFemale.KITTEN_ID 
                                AND maleName.NAME = 'Name'
LEFT JOIN KITTEN_INFO maleName ON maleName.KITTEN_ID = lastMale.KITTEN_ID 
                              AND femaleName.NAME = 'Name'
    WHERE lastMale.PLAY_TIME BETWEEN '01-JAN-2011 12:00:00 AM'
                                 AND '31-JAN-2011 11:59:59 PM'

Problems

This does not account for the fact that a cat may have played with a make/female kitten more than once. So, I'd want to add "AND lastMale.PLAY_TIME = (SELECT MAX(PLAY_TIME) FROM CAT_KITTEN_PLAY WHERE CAT_ID = cat.ID AND KITTEN_GENDER = 'M')" onto the first join, and a similar one to the second join. But subqueries are not allowed on joins.

Any thoughts? Note that a cat may have never played with a female kitten. But the cat should still be included if they meet the criteria (hence the left joins).

Analytic functions are probably the best answer to your problem:

SELECT DISTINCT
       cat.*, 
       first_value(lastMale.PLAY_TIME) 
          over (partition by cat.id 
                order by lastMale.PLAY_TIME desc nulls last) maleTime,
       first_value(lastFemale.PLAY_TIME) 
          over (partition by cat.id 
                order by lastFemale.PLAY_TIME desc nulls last) femaleTime,
       first_value(maleName.VALUE)  
          over (partition by cat.id 
                order by lastMale.PLAY_TIME desc nulls last) male,
       first_value(femaleName.VALUE)  
          over (partition by cat.id 
                order by lastFemale.PLAY_TIME desc nulls last) female
...

Just times of last play, not names of Kittens:

SELECT cat.CAT_ID
     , MAX(play.PLAY_TIME) AS maleTime
     , ( SELECT MAX(playf.PLAY_TIME)
         FROM CAT_KITTEN_PLAY AS playf
         WHERE playf.CAT_ID = cat.CAT_ID
           AND playf.KITTEN_GENDER = 'F'
       ) AS femaleTime
FROM CAT AS cat
  JOIN CAT_KITTEN_PLAY AS play
    ON play.CAT_ID = cat.CAT_ID
WHERE play.KITTEN_GENDER = 'M'
GROUP BY cat.CAT_ID 
HAVING MAX(play.PLAY_TIME) BETWEEN '01-JAN-2011 12:00:00 AM'
                               AND '31-JAN-2011 11:59:59 PM'

Not at all sure that this will work:

SELECT cat.CAT_ID
     , lastplayM.PLAY_TIME AS maleTime
     , lastplayF.PLAY_TIME AS femaleTime
     , kittenM.VALUE AS male
     , kittenF.VALUE AS female
FROM CAT AS cat
  JOIN 
    ( SELECT CAT_ID
           , KITTEN_GENDER
           , KITTEN_ID
           , MAX(PLAY_TIME) OVER(PARTITION BY CAT_ID, KITTEN_GENDER) AS PLAY_TIME
      FROM CAT_KITTEN_PLAY
      WHERE PLAY_TIME = MAX(PLAY_TIME) OVER(PARTITION BY CAT_ID, KITTEN_GENDER)
    ) AS lastplayM
    ON lastplayM.CAT_ID = cat.CAT_ID
    AND lastplayM.KITTEN_GENDER = 'M'
    AND lastplayM.PLAY_TIME BETWEEN '01-JAN-2011 12:00:00 AM'
                               AND '31-JAN-2011 11:59:59 PM'
  JOIN KITTEN_INFO AS kittenM
    ON kittenM.KITTEN_ID = lastplayM.KITTEN_ID

  LEFT JOIN 
    ( SELECT CAT_ID
           , KITTEN_GENDER
           , KITTEN_ID
           , MAX(PLAY_TIME) OVER(PARTITION BY CAT_ID, KITTEN_GENDER) AS PLAY_TIME
      FROM CAT_KITTEN_PLAY
      WHERE PLAY_TIME = MAX(PLAY_TIME) OVER(PARTITION BY CAT_ID, KITTEN_GENDER)
    ) AS lastplayF
    ON lastplayF.CAT_ID = cat.CAT_ID
    AND lastplayF.KITTEN_GENDER = 'F'
  JOIN KITTEN_INFO AS kittenF
    ON kittenF.KITTEN_ID = lastplayF.KITTEN_ID

You could use the WITH clause to make this somewhat easier

WITH MAX_MALE_KITTEN AS
(

     SELECT
        CAT_ID,
        MAX(PLAY_TIME) PLAY_TIME
     FROM
        CAT_KITTEN_PLAY
     WHERE
        KITTEN_GENDER = 'M'
     GROUP BY
        CAT_ID
),
MAX_FEMALE_KITTEN AS
(

     SELECT
        CAT_ID,
        MAX(PLAY_TIME) PLAY_TIME
     FROM
        CAT_KITTEN_PLAY
     WHRE
        KITTEN_GENDER = 'F'
     GROUP BY
        CAT_ID
)

SELECT
    C.CAT_ID,
    F_K_I.NAME LastFemaleName,
    F_PLAY_INFO.PLAY_TIME LastFemalePlayTime,
    M_K_I.NAME LastMaleName,
    M_PLAY_INFO.PLAY_TIME LastMalePlayTime

FROM
    CAT C
    LEFT JOIN CAT_KITTEN_PLAY F_PLAY_INFO
    ON C.CAT_ID = F_PLAY_INFO.CAT_ID
    AND F_PLAY_INFO.KITTEN_GENER= 'F'
    LEFT JOIN MAX_FEMALE_KITTEN M_F_K
    ON F_PLAY_INFO.CAT_ID = M_F_K.CAT_ID
    AND F_PLAY_INFO.PLAY_TIME = M_F_K.PLAY_TIME
    LEFT JOIN KITTEN_INFO F_K_I
    ON F_PLAY_INFO.KITTEN_ID = F_K_I.KITTEN_ID

    INNER JOIN CAT_KITTEN_PLAY M_PLAY_INFO
    ON C.CAT_ID = M_PLAY_INFO.CAT_ID
    AND M_PLAY_INFO.KITTEN_GENER= 'M'
    LEFT JOIN MAX_MALE_KITTEN M_F_K
    ON M_PLAY_INFO.CAT_ID = M_F_K.CAT_ID
    AND M_PLAY_INFO.PLAY_TIME = M_F_K.PLAY_TIME
    LEFT JOIN KITTEN_INFO M_K_I
    ON M_PLAY_INFO.KITTEN_ID = M_K_I.KITTEN_ID
WHERE 
       MAX_MALE_KITTEN.PLAY_TIME    BETWEEN '01-JAN-2011 12:00:00 AM'
                           AND '31-JAN-2011 11:59:59 PM