Results 1 to 9 of 9
  1. #1
    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. #2
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    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. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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

    As in AM?

    you just getting in?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Rudy has no need for sleep.
    Sleep is inefficient.


    George
    Home | Blog

  7. #7
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote 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?

    -- This is all just a Figment of my Imagination --

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    indeed, sometimes i get sleepy well before it gets dark!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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