Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    4

    Unanswered: getting uniqe rows from multi table query

    Hi There,

    I'm a little lost. I know how to join these 3 tables but there are duplicate rows in the results. How can I get all the unique rows to only show up once.

    My query is:

    SELECT tbl_rate.rate_ID, tbl_rate.rate_site_IDref, tbl_rate.rate_rank_IDref, tbl_rate.rate_dollar, tbl_rate.rate_ot, tbl_rate.rate_dt, tbl_rate.rate_loa,
    tbl_employee.employee_name, tbl_LEM.LEM_date, tbl_LEM.LEM_reg_hrs, tbl_LEM.LEM_ot_hrs, tbl_LEM.LEM_dt_hrs, tbl_LEM.LEM_tt_hrs, tbl_LEM.site_IDREF,
    tbl_LEM.LEM_LOA, tbl_LEM.LEM_Expenseinfo, tbl_LEM.LEM_workorder, tbl_LEM.LEM_posted, tbl_LEM.LEM_Expense, tbl_LEM.LEM_tagnumberREF,
    tbl_LEM.LEM_reg_rate, tbl_LEM.Lem_ot_rate, tbl_LEM.Lem_tt_rate, tbl_LEM.LEM_dt_rate, tbl_LEM.Lem_loa_rate, tbl_LEM.LEM_ID, tbl_LEM.LEM_equip_days

    FROM tbl_rate INNER JOIN
    tbl_employee ON tbl_rate.rate_rank_IDref = tbl_employee.employee_rankREF INNER JOIN
    tbl_LEM ON tbl_employee.employee_ID = tbl_LEM.employee_IDREF


    The results somehow need to be distinct by tbl_lems.lem_ID


    Thank you in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please tell me the cardinalities of your relationships

    rate to employees -- one-to-many?

    employee to rates -- one-to-one ??

    lem to employees -- ????

    employee to lems -- ????

    what's a lem?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2012
    Posts
    4

    the info you asked for

    please tell me the cardinalities of your relationships

    rate to employees -- one-to-many?

    employee to rates -- one-to-one ??

    lem to employees -- ????

    employee to lems -- ????

    what's a lem?


    Here are the relationships:
    They are all one to many... however, tbl_employees references both rates and lems. Lems does not relate to rates

    A L.E.M. is basically a daily time sheet. On it is Labour, Equipment, and Material (L.E.M) that the sub contractor uses for both invoicing purposes and in-house payroll, budgeting, statistics, etc.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by clawr687 View Post
    They are all one to many...
    okay, but then you also want this --
    The results somehow need to be distinct by tbl_lems.lem_ID
    see, there's a problem with this

    if each employee has many lems, and you want the results to be distinct for each lem, where are the duplicates coming from?

    something is not quite right

    what are the primary keys in your tables?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2012
    Posts
    4

    I figured it out

    Thanks for your help but I figured out my problem.

    I had to draw it out on paper but then I realised that I was joining on the wrong column on one of the tables.

    Thanks for your help though. I really appreciate it.

Posting Permissions

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