Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Red face Unanswered: Sql help: Inner joins

    Hello,

    Hope you can help as I am kind of stuck with this SQL :-(

    I have 3 tables as follow:

    Table: Customer
    Cust_ID, Emp_Status_id, resident_status_id, Credit_status_id, Active_status
    1 0 1 0 1
    2 0 3 10 1
    3 3 5 80 0
    4 5 3 80 0
    5 2 3 90 1


    Table: RefType
    Ref_Type, Ref_type_id
    EmpStatusId 10001
    ResidentId 50005
    CreditId 30200

    Table: RefCode
    Ref_Code_id, Ref_id, Ref_description
    10001 0 Unemployed
    10001 1 Home Duties
    10001 2 Self Employed
    10001 3 Student
    10001 4 Contractor
    10001 5 Business Owner
    10001 6 Government Sector
    10001 7 Manager
    50005 1 Citizen
    50005 2 Study Visa
    50005 3 Working Visa
    50005 4 Holiday Visa
    50005 5 Temporary Visa
    30200 0 No Credit Rating
    30200 10 Low Credit Rating
    30200 50 Average Credit Rating
    30200 80 Good Credit Rating
    30200 90 Very Good Credit Rating
    30200 100 Execllent Credit Rating

    As can be seen, table Customer has customer records with employment status id, residential status id and credit status id. These IDs are referenced to tables RefType and RefCode.

    CustomerId 1 has Emp_status_id 0. To get the description for Emp_status_id, i Need to go to RefType (Ref_Type='EmpStatusId') and then to RefCode.

    I have this SQL. Why is not working?

    select c.cust_id, c.emp_status_id, rc.ref_description,
    c.resident_status_id, rc.ref_description,
    c.credit_status_id, rc.ref_description
    from customer
    INNER
    JOIN refCode rc, refType rt
    ON c.emp_status_id = rc.ref_id, rc.ref_id = rt.ref_type_id, rt.ref_type = 'EmpStatusId'
    INNER
    JOIN refCode rc, refType rt
    ON c.resident_status_id = rc.ref_id, rc.ref_id = rt.ref_type_id, rt.ref_type = 'ResidentId'
    INNER
    JOIN refCode rc, refType rt
    ON c.credit_status_id = rc.ref_id, rc.ref_id = rt.ref_type_id, rt.ref_type = 'CreditId'
    where Active_status = 1

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hughtran View Post
    I have this SQL. Why is not working?
    because your join conditions are all messed up

    here, i re-arranged them for you...
    Code:
    SELECT customer.cust_id
         , customer.emp_status_id
         , rcE.ref_description    emp_status
         , rcR.ref_description    resident_status 
         , rcC.ref_description    credit_status
      FROM customer
    INNER 
      JOIN refCode rcE
        ON rcE.ref_id = customer.emp_status_id
    INNER
      JOIN refType rtE
        ON rtE.ref_type_id = rcE.ref_id 
       AND rtE.ref_type = 'EmpStatusId'
    INNER 
      JOIN refCode rcR
        ON rcR.ref_id = customer.resident_status_id
    INNER
      JOIN refType rtR
        ON rtR.ref_type_id = rcR.ref_id
       AND rtR.ref_type = 'ResidentId'
    INNER 
      JOIN refCode rcC
        ON rcC.ref_id = customer.credit_status_id 
    INNER
      JOIN refType rtC
        ON rtC.ref_type_id = rcC.ref_id 
       AND rtC.ref_type = 'CreditId'
     WHERE customer.Active_status = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2011
    Posts
    2

    Thank you!

    Legend! Thanks for your help with the SQL.


    Cheers...

Posting Permissions

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