August 27, 2004 at 6:34 am
I have a database that holds details of service calls and related visits. There are 2 tables (amongst others)
that look like the following.....
CALLS
-----
JOB_NUMBER | LOG_DATE | etc....
-----------------------------------
1 |2004-08-08 |
2 |2004-06-07 |
3 |2004-09-12 |
VISITS
------
JOB_NUMBER | VISIT_NUMBER | ENGINEER
------------------------------------
1 | 1 | John
1 | 2 | Bob
2 | 1 | Greg
3 | 1 | John
1 | 3 | Greg
2 | 2 | Bob
What I want to do is simply end up with a result set that has all the columns of the CALLS db,
with a new column added which holds the name of the engineer of the first visit for that job.
What is the best way of doing this?
August 27, 2004 at 6:55 am
SELECT c.*, v.ENGINEER
FROM Calls c
LEFT JOIN VISITS v ON v.JOB_NUMBER = c.JOB_NUMBER AND VISIT_NUMBER = 1
The Left Join will prevent eliminating Job_number's that no one has made a visit to. If the intent is to eleminate these Job_Number's then change this to an "INNER JOIN". If the ENGINEER column is NULL then no one has made a visit on this Job_Number.
August 27, 2004 at 7:01 am
thanks for the quick response!
how about if the first visit is not necessarily number 1? for example, some visits may be deleted which means what I'm actually looking for is the LOWEST visit number.......
August 27, 2004 at 7:16 am
SELECT
c.JOB_NUMBER,
c.LOG_DATE,
c.ETC,
ENGINEER = (SELECT
ENGINEER
FROM VISITS v
WHERE v.JOB_NUMBER = c.JOB_NUMBER AND v.VISIT_NUMBER = (SELECT MIN(VISIT_NUMBER) FROM VISITS))
FROM Calls c
August 27, 2004 at 7:32 am
yeah - i got to this conclusion to. problem is, with that many sub queries it takes a very long time to run. the calls table has about 5000 lines and the visit table has 7000.
also i think the query should be:
SELECT
c.JOB_NUMBER,
c.LOG_DATE,
c.ETC,
ENGINEER = (SELECT
ENGINEER
FROM VISITS v
WHERE v.JOB_NUMBER = c.JOB_NUMBER AND v.VISIT_NUMBER = (SELECT MIN(VISIT_NUMBER) FROM VISITS WHERE JOB_NUMBER = c.JOB_NUMBER ))
FROM Calls c
surely we're missing a better-performing solution??????
August 27, 2004 at 7:48 am
The problem is indexes then. Either create or check the health of the indexes on "Calls.JOB_NUMBER", "VISITS.JOB_NUMBER", "VISITS.VISIT_NUMBER". Unless you are on a very slow box this should in 1-2 seconds.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply