Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    27

    Unanswered: quick query question

    Greetings all,

    I have this below query and I am trying to return records for a given foreign key with the VENDOR_ID value of 'US' & 'HP'. The returning result need to return just those records that have both values, not one or the other. I know the holdup is in this clause 'and (d.VENDOR_ID = 'US' and d.VENDOR_ID = 'HP') ' What must I do to get the result set I desire? Use a group by, having clause? A double join on the DISCOUNT table? Any help would be greatly be appreciated.

    select FIRST_NAME, CUSTOMER_MI, LAST_NAME, EMAIL_ADDRESS, d.VENDOR_ID, DISCOUNT_NO, PROGRAM_NAME
    from CUSTOMER as c1, EMAIL as e, DISCOUNT as d
    where c1.CUSTOMER_ID *= e.CUSTOMER_ID
    and c1.CUSTOMER_ID = d.CUSTOMER_ID
    and d.DISCOUNT_CODE = 'AFTN'
    and (d.VENDOR_ID = 'US' and d.VENDOR_ID = 'HP')
    and d.SERVICE_TYPE = 'A'
    and e.EMAIL_TYPE = 'INET'
    order by LAST_NAME

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    One row in "DISCOUNT as d" can not contain both 'US' and 'HP'. Change to:
    and (d.VENDOR_ID = 'US' or d.VENDOR_ID = 'HP')

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select FIRST_NAME
         , CUSTOMER_MI
         , LAST_NAME
         , EMAIL_ADDRESS
         , d.VENDOR_ID
         , DISCOUNT_NO
         , PROGRAM_NAME
      from CUSTOMER as c1
    inner
      join ( 
           select VENDOR_ID
             from DISCOUNT 
            where VENDOR_ID IN ('US','HP') 
              and DISCOUNT_CODE = 'AFTN' 
              and SERVICE_TYPE = 'A'
           group
               by VENDOR_ID
           having count(*) = 2    
           ) as d
        on c1.CUSTOMER_ID = d.CUSTOMER_ID 
    left outer
      join EMAIL as e
        on c1.CUSTOMER_ID = e.CUSTOMER_ID 
       and e.EMAIL_TYPE = 'INET'
    order 
        by LAST_NAME
    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
  •