Results 1 to 2 of 2

Thread: Full Outer Join

  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Full Outer Join

    Greetings:

    Having a little bit of trouble with a JON clause and would appreciate any help or advice anyone can give.

    Here's the original query:

    SELECT AIM.AIMRETRIEVAL.AIMRETRIEVALID, AIM.AIMRETRIEVAL.DESCRIPTION, AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID, AIM.ARCHIVERETRIEVAL.STATUSID,
    AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE, AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME, AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT, AIM.ARCHIVERETRIEVAL.ITEMCOUNT,
    (SELECT AIM.ARCHIVERETRIEVAL.ITEMCOUNT - COUNT(AIM.AIMRETRIEVEDITEM.AIMRETRIEVEDITEMID) FROM AIM.AIMRETRIEVEDITEM WHERE
    (AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID = AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID) AND
    LENGTH(AIM.AIMRETRIEVEDITEM.IMAGEFRONT) IS NULL)
    AS NUMBERIMAGESAVAILABLE
    FROM
    AIM.AIMCONNECTION,
    AIM.AIMQUERY,
    AIM.AIMRETRIEVAL,
    AIM.ARCHIVERETRIEVAL,
    AIM.AIMRETRIEVEDITEM,
    AIM.ARCHIVETYPE,
    AIM.CHANNEL
    WHERE
    AIM.AIMCONNECTION.AIMCONNECTIONID = AIM.AIMQUERY.AIMCONNECTIONID
    AND AIM.AIMQUERY.AIMQUERYID = AIM.AIMRETRIEVAL.AIMQUERYID
    AND AIM.AIMRETRIEVAL.AIMRETRIEVALID = AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
    AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID = AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
    AND AIM.ARCHIVERETRIEVAL.ARCHIVEID = AIM.ARCHIVETYPE.ARCHIVEID
    AND (AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE >= DATE(CURRENT TIMESTAMP) OR (AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE IS NULL AND AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME >= CURRENT TIMESTAMP))
    AND AIM.ARCHIVETYPE.ARCHIVETYPE = 'D'
    AND AIM.AIMCONNECTION.USERID = strUserID
    AND AIM.CHANNEL.CHANNEL =
    (SELECT CHANNEL FROM AIM.CHANNEL, AIM.CHANNELSESSION, AIM.AIMCONNECTION WHERE
    AIM.CHANNELSESSION.CHANNELSESSIONID = AIM.AIMCONNECTION.CHANNELSESSIONID
    AND AIM.CHANNELSESSION.CHANNELID = AIM.CHANNEL.CHANNELID
    AND AIM.AIMCONNECTION.AIMCONNECTIONID = decAIMConnectionID)
    GROUP BY AIM.AIMRETRIEVAL.AIMRETRIEVALID, AIM.AIMRETRIEVAL.DESCRIPTION, AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID, AIM.ARCHIVERETRIEVAL.STATUSID,
    AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE, AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME, AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT, AIM.ARCHIVERETRIEVAL.ITEMCOUNT,
    AIM.ARCHIVERETRIEVAL.ITEMCOUNT
    ORDER BY AIMRETRIEVALID
    FOR FETCH ONLY WITH UR;

    This query returns 7 rows. Now, I've added a new table to the relationship, DELIVERY, and need to join on two columns in that table: STATUSID and DELIVERYMETHODID. Here's the issue: old data in the parent table (ARCHIVERETRIEVAL) doesn't have a corresponding row in the child table - DELIVERY. When joining, those old rows don't get retrieved.

    So I added a couple of FULL OUTER JOINS. I want to retrieve the rows that match the other criteria, even if those rows do not have a corresponding row in DELIVERY. Here's my new queery:

    SELECT AIM.AIMRETRIEVAL.AIMRETRIEVALID,
    AIM.AIMRETRIEVAL.DESCRIPTION,
    AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID,
    AIM.ARCHIVERETRIEVAL.STATUSID,
    AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE,
    AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME,
    AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT,
    AIM.ARCHIVERETRIEVAL.ITEMCOUNT,
    (SELECT AIM.ARCHIVERETRIEVAL.ITEMCOUNT - COUNT(AIM.AIMRETRIEVEDITEM.AIMRETRIEVEDITEMID) FROM AIM.AIMRETRIEVEDITEM WHERE
    (AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID = AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID) AND
    LENGTH(AIM.AIMRETRIEVEDITEM.IMAGEFRONT) IS NULL)
    AS NUMBERIMAGESAVAILABLE,
    AIM.DELIVERYMETHOD.DELIVERYMETHOD,
    AIM.DELIVERY.DELIVERYADDRESS,
    AIM.DELIVERY.DELIVERYDATE,
    S1.STATUS AS DELIVERYSTATUS,
    AIM.DELIVERY.VALIDATIONSTATUSID
    FROM
    AIM.AIMCONNECTION,
    AIM.AIMQUERY,
    AIM.AIMRETRIEVAL,
    AIM.ARCHIVERETRIEVAL,
    AIM.AIMRETRIEVEDITEM,
    AIM.ARCHIVETYPE,
    AIM.CHANNEL,
    AIM.DELIVERY,
    AIM.DELIVERYMETHOD,
    AIM.STATUS AS S1 FULL OUTER JOIN AIM.DELIVERY AS DELIVERY ON S1.STATUSID = DELIVERY.STATUSID
    FULL OUTER JOIN AIM.DELIVERYMETHOD AS DELIVERYMETHOD ON DELIVERY.DELIVERYMETHODID = DELIVERYMETHOD.DELIVERYMETHODID
    WHERE
    AIM.AIMCONNECTION.AIMCONNECTIONID = AIM.AIMQUERY.AIMCONNECTIONID
    AND AIM.AIMQUERY.AIMQUERYID = AIM.AIMRETRIEVAL.AIMQUERYID
    AND AIM.AIMRETRIEVAL.AIMRETRIEVALID = AIM.ARCHIVERETRIEVAL.AIMRETRIEVALID
    AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID = AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
    AND AIM.ARCHIVERETRIEVAL.ARCHIVEID = AIM.ARCHIVETYPE.ARCHIVEID
    AND S1.STATUSID = AIM.DELIVERY.STATUSID
    AND AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID = AIM.DELIVERY.ARCHIVERETRIEVALID
    AND AIM.DELIVERY.DELIVERYMETHODID = AIM.DELIVERYMETHOD.DELIVERYMETHODID
    AND (AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE >= DATE(CURRENT TIMESTAMP) OR (AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE IS NULL AND AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME >= CURRENT TIMESTAMP))
    AND AIM.ARCHIVETYPE.ARCHIVETYPE = 'D'
    AND AIM.AIMCONNECTION.USERID = strUserID
    AND AIM.CHANNEL.CHANNEL =
    (SELECT CHANNEL FROM AIM.CHANNEL, AIM.CHANNELSESSION, AIM.AIMCONNECTION WHERE
    AIM.CHANNELSESSION.CHANNELSESSIONID = AIM.AIMCONNECTION.CHANNELSESSIONID
    AND AIM.CHANNELSESSION.CHANNELID = AIM.CHANNEL.CHANNELID
    AND AIM.AIMCONNECTION.AIMCONNECTIONID = decAIMConnectionID)
    GROUP BY AIM.AIMRETRIEVAL.AIMRETRIEVALID,
    AIM.AIMRETRIEVAL.DESCRIPTION,
    AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID,
    AIM.ARCHIVERETRIEVAL.STATUSID,
    AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE,
    AIM.ARCHIVERETRIEVAL.ESTIMATEDRESPONSETIME,
    AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALRECEIPT,
    AIM.ARCHIVERETRIEVAL.ITEMCOUNT,
    S1.STATUS,
    AIM.DELIVERYMETHOD.DELIVERYMETHOD,
    AIM.DELIVERY.DELIVERYADDRESS,
    AIM.DELIVERY.DELIVERYDATE,
    AIM.DELIVERY.VALIDATIONSTATUSID
    ORDER BY AIMRETRIEVALID
    FOR FETCH ONLY WITH UR;

    This query only returns one row - the row that hjas a corresponding entry in DELIVERY.

    I hope all this made any kind of sense!!

    Thanks!
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use LEFT OUTER JOIN. It will include all rows in the Left table and for those rows in the Right table that do not match, null will be used for any columns in the result set that are derived from the Right table.

    HTH

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •