# Thread: Complex Join, I think.

1. Registered User
Join Date
Jun 2007
Posts
4

## Unanswered: 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!

2. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
Code:
```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```

3. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
Provided Answers: 1
Code:
```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
Last edited by pdreyer; 06-28-07 at 03:58. Reason: PS

4. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
Originally Posted by pdreyer
PS. There is something amiss with Rudy's query
indeed there was, 2 things amiss

try it now --
Code:
```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```

5. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
4:30?

As in AM?

you just getting in?

6. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Provided Answers: 12
Rudy has no need for sleep.
Sleep is inefficient.

7. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
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

8. 9th inning DBA
Join Date
Jan 2004
Location
In a large office with bad lighting
Posts
1,040
Originally Posted by r937
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?

9. SQL Consultant
Join Date
Apr 2002
Location
Toronto, Canada
Posts
20,002
indeed, sometimes i get sleepy well before it gets dark!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•