Complex Join, I think.

I need a little help and I hope I can write a clear description of the problem. I have to tables, customers and custrate. The customer table simply has a custid of other misc info. The custrate contains an entry for each rate for any particular customer (1 customer to many rates). However the rates are suppossed to come in pairs. For example, if a customer has a rate id of 120 then the customer should also have a corresponding custrate record with a rate_id of 200. If the customer has a rate_id of 130, then the customer should have a corresponding custrate record containing rate_id 300, and so on and so forth. How do I find the customer id with are missing the corresponding rate? Here's an example or what I'm trying to explain.

customer
id | name
1 | fred
2 | tom
3 | eric
4 | fred

custrate
cust_id | rate_id
1 | 120
1 | 200
2 | 130
2 | 300
3 | 120
4 | 130

I looking for the sql statement that would return customer id of 3 and 4, since those are the only 2 records that don't have a corresponding rate. I have hunch that it requires a intra table join, but maybe i'm totally wrong. Can anyone help me please? I would greatly appreciate it!select c.name
from customer as c
inner
join custrate as cr1
on cr1.cust_id = c.id
and cr1.rate_id in (120,130)
inner
join custrate as cr2
on cr2.cust_id = c.id
and cr2.rate_id =
case when cr1.rate_id = 120
then 200
case when cr1.rate_id = 130
then 300
end
where cr2.cust_id is null|||select * from customer c
where not exists
(select 1 from custrate r
where c.id=r.cust_id
group by r.cust_id
having count(*)%2=0 -- Must be paired
)
PS. There is something amiss with Rudy's query|||PS. There is something amiss with Rudy's queryindeed there was, 2 things amiss

try it now --select c.name
from customer as c
inner
join custrate as cr1
on cr1.cust_id = c.id
and cr1.rate_id in (120,130)
left outer
join custrate as cr2
on cr2.cust_id = c.id
and cr2.rate_id =
case when cr1.rate_id = 120
then 200
when cr1.rate_id = 130
then 300
end
where cr2.cust_id is null:)|||4:30?

As in AM?

you just getting in?|||Rudy has no need for sleep.
Sleep is inefficient.

:p|||sorry for the late reply, was out all day

yeah, i normally get up around 4:00 or 4:30 am

go to bed when it gets dark

:)|||sorry for the late reply, was out all day

yeah, i normally get up around 4:00 or 4:30 am

go to bed when it gets dark

:)

You must have a heck of a time during the summer when it doesn't get dark until after 10:00 PM ... or is it about 10:30 in your area?|||indeed, sometimes i get sleepy well before it gets dark!