Joining tables

Question

Post reply

Joining tables

bjh1977

Right there with Babe

Points: 740
More actions
August 27, 2004 at 6:34 am

#109227

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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Stephen Norrod SSC Veteran Points: 280 More actions · Answer 1

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.

bjh1977 Right there with Babe Points: 740 More actions · Answer 2

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.......

Stephen Norrod SSC Veteran Points: 280 More actions · Answer 3

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

bjh1977 Right there with Babe Points: 740 More actions · Answer 4

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??????

Stephen Norrod SSC Veteran Points: 280 More actions · Answer 5

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.