TAGS :Viewed: 7 - Published at: a few seconds ago

[ How to JOIN on my Result Set and a Third Table? ]

I'm trying to figure out how to perform a JOIN where I return all the records from my Result set (query 1) below where a.ssan = c.ssn:

Query 1:

SELECT 
a.memno, 
a.name, 
a.addr1, 
a.addr2, 
a.city, 
a.state, 
a.zip, 
a.sex, 
a.lname, 
a.ssan, 
b.addr1 as old_addr1, 
b.addr2 as old_addr2, 
b.city as old_city, 
b.state as old_state, 
b.zip as old_zip, 
b.timec 

FROM Lib1.Table1 a, Lib1.Table2 b 

WHERE 
a.memno = b.memno and 
b.groupid = 'P2' and 
b.type = 'B' and 
b.datec = 20131206 AND 
(a.addr1 <> b.addr1 or 
a.addr2 <> b.addr2 or 
a.city <> b.city or 
a. state <> b.state or 
a.zip <> b.zip)

Table C fields:

SSN
DATEC
TIMEC
PRINT_OLD
PRINT_NEW

I'm generating mail merge documents for NEW and OLD Addresses to alert members of an address change made on their account. When the NEW Address Letters are made, I run an insert on TABLEC setting the values of all the fields except PRINT_OLD. Example data would be:

SSN: 123456789; 
DATEC: 20131206; 
TIMEC: 133000; 
PRINT_NEW: 2013-12-06-13.30.00.965000; 
PRINT_OLD: NULL;

Then several days later, when going to print the OLD Address letters, I need to run the first query above combined with a JOIN on TTABLEC to display those records that the PRINT_OLD field is NULL, denoting the NEW Address Letter has been printed already for that day, but the OLD Address Letter has not.

Can anyone help me with this matter? I'm not the strongest when it comes to Database queries.

Answer 1


You can do:

SELECT *
FROM tableC c
INNER JOIN (
  SELECT a.memno,
    a.NAME,
    a.addr1,
    a.addr2,
    a.city,
    a.STATE,
    a.zip,
    a.sex,
    a.lname,
    a.ssan,
    b.addr1 AS old_addr1,
    b.addr2 AS old_addr2,
    b.city AS old_city,
    b.STATE AS old_state,
    b.zip AS old_zip,
    b.timec
  FROM Lib1.Table1 a
  INNER JOIN Lib1.Table2 b ON a.memno = b.memno
  WHERE b.groupid = 'P2'
    AND b.type = 'B' AND b.datec = 20131206
    AND (
      a.addr1 <> b.addr1 OR a.addr2 <> b.addr2
      OR a.city <> b.city OR a.STATE <> b.STATE
      OR a.zip <> b.zip)
  ) a ON a.ssan = c.ssn
WHERE c.print_old IS NULL

Note: I also changed your JOIN to an explicit INNER JOIN.

Answer 2


Due to your reference to Lib1 and Lib2, I am assuming you are on an IBM midrange system, correct?

I take it that memno is unique in table1, and that {memno, groupid, type, datec} is unique in tableb.

 SELECT a.memno, 
        a.name, 
        a.addr1, 
        a.addr2, 
        a.city, 
        a.state, 
        a.zip, 
        a.sex, 
        a.lname, 
        a.ssan, 
        b.addr1 as old_addr1, 
        b.addr2 as old_addr2, 
        b.city as old_city, 
        b.state as old_state, 
        b.zip as old_zip,
        b.datec, 
        b.timec, 
        c.PRINT_OLD
        c.PRINT_NEW
   FROM Lib1.Table1 a
   JOIN Lib1.Table2 b   
           ON a.memno = b.memno
          and b.datec = 20131206
          AND (a.addr1, a.addr2, a.city, a.state, a.zip)
           <> (b.addr1, b.addr2, b.city, b.state, b.zip)
   JOIN Lib1.TableC c
           ON c.ssn = a.ssan
          and c.datec = b.datec
          and c.timec = b.timec
   WHERE c.PRINT_OLD is NULL                   
     and b.groupid = 'P2' 
     and b.type = 'B'
     and b.datec = 20131206

I suspect this is a bit closer to what you are looking for, and shows some different techniques.