Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Multiple joins between tables.

Status
Not open for further replies.

sholroyd

Programmer
Nov 25, 2003
4
GB
Hello!

I posted this question as a follow up to a previously answered question, and decided that this is completely different and should be treated as a new post (doh!).

Each peice of equipment can be tested many times and a certificate is given everytime it passes of fails. If the equipment passes then its date for recall is 1 year from the pass date.

Here's my SQL at the moment:

select
e.CATRef_num as "EQUI NUM",
MAX(tc.id_test_certificate) as "LAST TEST CERT NUM",
e.make as "MAKE",
c.id_customer as "CUSTOMER ID",
c.name as "CUSTOMER NAME",
el.date as "Last Test Date",
el.date + INTERVAL 1 YEAR as "RE-TEST DUE DATE"
from customer c, equipment e, equipment_location el, test_certificate tc
where e.CATRef_num = el.CATRef_num
and e.CATRef_num = tc.CATRef_num
and el.id_location = 6
and e.id_customer = c.id_customer
group by e.CATRef_num;

The id_location is 6 because this is the id reflecting the equipment passing the tests so requiring a recall.

This SQL is flawed because the equipment may have failed after having passed, so this SQL will pull out the passed certificate and ignore the fact it has been retested and failed.

Basically I need to report all of the equipment thats last test was a pass in april 2003 and will need to be retested in april 2004.

Here are the relevant creates:

create table CUSTOMER(
id_customer int(4) auto_increment primary key,
name varchar(40) not null,
address varchar(50) not null
);

create table EQUIPMENT(
CATRef_num int(4) auto_increment primary key,
id_customer int(4) not null,
id_category int(4) not null,
make vArchar(15) not null,
model varchar(40) not null,
serial_num int(30),
DOB date
);

create table EQUIPMENT_LOCATION(
id_equipment_location int(4) auto_increment primary key,
CATRef_num int(4) not null,
id_location int(4) not null,
date datetime
);

create table TEST_CERTIFICATE(
id_test_certificate int(4) auto_increment primary key,
CATRef_num int(4) not null,
id_engineer int(4) not null,
date date
);

Multiplicity:

1 customer has many equipment
1 equipment has many test_certificate
1 equipment has many equipment_location
 
Q1. Is the date in test_certifcate diff from equipment_location ? are both testing dates ? then u need only one

Q2. If the equipment fails the test before completion of the yr then how do u record it ? If u add 'status' test_certificate showing pass or fail (can be enum type)

Q3. Yr last two paras as understood by me

This SQL is flawed because the equipment may have failed after having passed, so this SQL will pull out the passed certificate and ignore the fact it has been retested and failed.

Basically I need to report all of the equipment thats last test was a pass in april 2003 and will need to be retested in april 2004.

as understood by me

if u just need the passed ones then why worry about failed test.

if u add status field then u can test for all passed equipment and the date should be from test_certifcate date. and not equipment_location as u are taking now

I think u take date i equipment_location as its date of installation at that location



[ponder]
----------------
ur feedback is a very welcome desire
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top