I have 3 tables that are similar:
Table PROBEVALIDATIONZAXIS
Columns:
timeofentry
jobnumber
zhit
machine
Table PROBEVALIDATIONHOLE
Columns:
timeofentry
jobnumber
zhit
machine
Table PROBEVALIDATIONCORNER
Columns:
timeofentry
jobnumber
zhit
machine
I want to display a distinct jobnumber for a certain machine but I also want to display a date. The problem I am having is that if I include the timeofentry in the select statement, then my select no longer displays a distinct job number because the time of entry is different in each table. How can I get it to display the min date from all 3 columns along with the job number?
If I run the query now (see below), I get the following:
11/14/2016 05:52:10 AM 28810
11/14/2016 05:52:16 AM 28810
11/14/2016 05:52:24 AM 28810
11/14/2016 06:52:10 AM 28811
11/14/2016 06:52:16 AM 28811
11/14/2016 06:52:24 AM 28811
What I want is the result with the minimum date for every job number:
11/14/2016 05:52:10 AM 28810
11/14/2016 06:52:10 AM 28811
I should also add that I originally had the union commands as minus commands, however this only works if the data is in all 3 tables. If it is just in 1 or 2 tables, then I don't get all the results
What I have tried:
Select timeofentry, JOBNUMBER
FROM PROBEVALIDATIONHOLE
WHERE HOLENUMBER = 1 AND TIMEOFENTRY between to_date('11/13/2016','MM/DD/YYYY HH:MI:SS AM') and to_date('11/14/2016 11:59:59 PM','MM/DD/YYYY HH:MI:SS AM') and machine = 'NC-500'
union
SELECT timeofentry, JOBNUMBER
FROM PROBEVALIDATIONZAXIS
WHERE ZHIT = 1 AND TIMEOFENTRY between to_date('11/13/2016','MM/DD/YYYY HH:MI:SS AM') and to_date('11/14/2016 11:59:59 PM','MM/DD/YYYY HH:MI:SS AM') and machine = 'NC-500'
union
SELECT timeofentry, JOBNUMBER
FROM PROBEVALIDATIONCORNER
WHERE TIMEOFENTRY between to_date('11/13/2016','MM/DD/YYYY HH:MI:SS AM') and to_date('11/14/2016 11:59:59 PM','MM/DD/YYYY HH:MI:SS AM') and machine = 'NC-500'