Select query with join on a single table 1

[sholroyd]

Hello!

I need to query a table that contains id_equipment, id_customer and id_category columns. Multiplicity: one customer submits many pieces of equipment. One piece of equipment belongs to one category.

I need to write a query to return all id_customer's that have only submitted equipment to category 2. I'm used to oracle with lovely nested selects etc, but my version of mysql, which is 4.0 doesn't allow them, so I'm lost in a world of joins.

What I have so far:

select distinct e1.id_customer
from equipment e1
INNER JOIN equipment e2 ON e1.id_customer = e2.id_customer
and e1.id_category = 2
and e2.id_category <> 2;

This is so wrong!

Help?

 

[r937]

try this --

[tt]select distinct
e1.id_customer
from equipment e1
left outer
join equipment e2
on e1.id_customer = e2.id_customer
and e2.id_category = 2
where e1.id_category = 2
and e2.id_customer is null[/tt]


rudy

http://r937.com/

 

[sholroyd]

That didn't work, thanks anyway!

Here's the table create.

You can forget all the columns other than the primary key,
id_customer and category.

The category can only be 1,2 or 3. At the moment the table
has 11 rows and only id_customer 1 has submitted equipment to category 2.

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(15) not null,
serial_num int(15),
DOB date,
);

 

[swampBoogie]

select distinct e1.id_customer
from equipment e1
left equipment e2
on e1.id_customer = e2.id_customer
and e2.id_category <> 2
where e2.id_customer is null
and e1.id_category = 2

 

[r937]

and e2.id_category <> 2

thanks, swampboogie

typo on my part, eh -- measure twice, cut once

 

[sholroyd]

Fantastic! Perhaps you can help with one more? These two have been playing havoc with my brain.

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 &quot;EQUI NUM&quot;,
MAX(tc.id_test_certificate) as &quot;LAST TEST CERT NUM&quot;,
e.make as &quot;MAKE&quot;,
c.id_customer as &quot;CUSTOMER ID&quot;,
c.name as &quot;CUSTOMER NAME&quot;,
el.date as &quot;Last Test Date&quot;,
el.date + INTERVAL 1 YEAR as &quot;RE-TEST DUE DATE&quot;
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