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!

Compatibility

Hi,

I work with database administration and I migrated some databases from 2000 to 2005 (simply backup and restore options).

I have tested issues about compatibility. The idea is migrating without update the application.

However, I have already seen some problems with compatibility. After migration, all databases stay with compatibility 80 inside de environment 2005 to avoid problems as, for exemple, the sintax to use hints, with 2005 the with clause isn't opptionaly.

The problem is that somethings isn't ok this way. We can't extract reports as disk usage, backup and restore events etc or generate diagrams for databases with compatibility 80 inside sql management studio.

The reccomendation is change de level for 90, repeat the action and after came back for 80 but we can't do this without impact the current activities. Do you know others problems with databases level 80 and 2005? I read that it's recommended that all databases are updtade for level 90, so I should update my application for changging. What do you think about this?

Thanks,

Karine.

If you want the full features of SQL 2005 Management Studio, then you need to set db compatibility for the database to 90. If not updating your app is more important, then keep it at 80. There's a lot of really cool new UI features and other functionality that depend on compat being at 90, so if it's possible to change your app, I'd do it. Just my opinion.|||

Thanks Greg, but do you know some documentation or paper that show or list UI features couldn't be used with database level (80) inside sql management studio?