Need Help With SQL Statement

[ronfranks1]

This SQL needs to return 1 NEW record, marked "NEW", and only 1 old record, marked "OLD", if one existed before. The problem is, I am returning multiple OLD records that have a different dates from table2.

This is what I want:

12345 New
12345 Old

This is what I don't want:

12345 New
12345 Old
12345 Old
12345 Old

Here is my script:

SELECT
distinct a.job_number ||'New'
FROM table1 a, table2 b
WHERE a.date = b.date
AND a.job_number = b.job_number;
UNION
SELECT
distinct a.job_number ||'Old'
FROM table1 a, table2 b
WHERE a.date < b.date
AND a.job_number = b.job_number;

Thanks in advance.

 

[r937]

SELECT DISTINCT 
       a.job_number ||
        case when a.date = b.date
             then 'New' else 'Old' end
  FROM table1 a
INNER
  JOIN table2 b
    ON b.job_number = a.job_number
   AND b.date >= a.date

r937.com | rudy.ca