Thread: Help with query

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    3
    Rep Power
    0

    Help with query


    Hi
    in my database i have 3 tables
    customer -> cust_id , customer name , address
    work -> cust_id, work_id, date done
    work_labour -> work_id, hours charged, description.

    I need to get a query that will show me all the work done for all customers

    customer , work date , hours charged
    customer , work date , hours charged
    ect

    But im struggling with the query . I have tried
    SELECT *
    FROM customers
    RIGHT JOIN work
    ON customers.cust_id=work.work_id RIGHT JOIN work_lab ON work.work_id = work_lab.work_id
    But the output is all wrong and not showing what i need.

    Please help
    Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,376
    Rep Power
    391
    Code:
      ON customers.cust_id=work.work_id
    presumably you are not using the right column in the work table in the join condition.

    Could you explain why you are using right join? It does not seem appropriate in this context.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    3
    Rep Power
    0
    Originally Posted by swampBoogie
    Code:
      ON customers.cust_id=work.work_id
    presumably you are not using the right column in the work table in the join condition.

    Could you explain why you are using right join? It does not seem appropriate in this context.
    Im using right join because i got it wrong,

    so it should be
    SELECT *
    FROM customers
    JOIN work
    ON customers.cust_id=work.work_id

    but customers.cust_id is different from work_id they dont match and it dosent link in the 3rd table
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Presumably, what you're after is some subset of the following...
    Code:
    SELECT * 
      FROM customer c 
      LEFT 
      JOIN work w
        ON w.cust_id = c.cust_id
      LEFT
      JOIN work_labour wl
        ON wl.work_id = w.work_id;
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    3
    Rep Power
    0
    Thanks , i got the records i need

IMN logo majestic logo threadwatch logo seochat tools logo