Results 1 to 3 of 3

Thread: Where to go

  1. #1
    Join Date
    Sep 2013
    Posts
    57

    Unanswered: Where to go

    The question
    Display, by company, the company name, contact name and phone number for any payer who has no insureds.

    (now, I believe a company is the payer, yet can still have no insureds, if that makes sense.)

    The ERD
    http://s23.postimg.org/5zo1dajw7/image.jpg

    What I've tried
    Code:
    SELECT PAYER.COMPANY_NAME, CONTACT_NAME, PHONE_NUM
    FROM PAYER
    JOIN PLAN ON PAYER.PAYER_ID = PLAN.PAYER_ID
    JOIN POLICY ON PLAN.POLICY_ID =POLICY.POLICY_ID
    JOIN INSURED ON POLICY.POLICY_ID = INSURED.POLICY_ID
    JOIN MORTAL ON INSURED.MORTAL_ID = MORTAL.MORTAL_ID
    Yea, don't ask where i was going with this..........it isn't good

    Code:
    SQL> SELECT PAYER.COMPANY_NAME, PAYER.CONTACT_NAME, PAYER.PHONE_NUM
      2  FROM PAYER
      3  JOIN PLAN ON PLAN.PAYER_ID = PAYER.PAYER_ID
      4  JOIN POLICY ON POLICY.PLAN_ID = PLAN.PLAN_ID
      5  LEFT OUTER JOIN INSURED ON INSURED.POLICY_ID = POLICY.POLICY_ID
      6  WHERE
      7  INSURED.POLICY_ID IS NULL;
    
    no rows selected
    Thats the closest I could get,

    INSURED TABLE
    Code:
    SQL> select * from insured;
    
    INSURED_ID I  POLICY_ID  MORTAL_ID
    ---------- - ---------- ----------
           602 P        501        102
           603 s        501        103
           604 c        501        104
           605 p        502        105
           606 S        502        106
           607 C        502        107
           608 P        503        108
           609 c        503        109
           610 p        504        110
           611 C        504        111
           612 P        505        112
           613 s        505        113
           614 c        506        114
           615 C        506        115
           616 P        507        116
           617 S        507        117
           618 c        507        118
           619 p        508        119
           620 C        508        120
    The thing is, Im trying to get a list of entites where there is no insurance likely a company from payer, then a mortal?
    Attached Thumbnails Attached Thumbnails zz.jpg  

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT PO_ID FROM PURCHASE_ORDER
    MINUS
    SELECT PO_ID FROM PURCHASE_ORDER_DETAIL;

    (PURCHASE_ORDER_DETAIL.PO_ID is FK point back to parent record containing PURCHASE_ORDER.PO_ID Primary Key)

    above lists all Purchase Order records which do NOT have any Detail records associated with it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2013
    Posts
    57
    Thanks, figured it out, sorry didn't reply earlier, classes, work, etc.

Posting Permissions

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