Thread: Joining issue

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

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0

    Joining issue


    Hi everyone. I'm fairly new, mediocre at best and I have a joining problem. I currently have 2 queries right now.
    rs_getrate:
    SELECT *
    FROM dbo.tbl_rate
    JOIN dbo.tbl_site ON dbo.tbl_site.site_ID = dbo.tbl_rate.rate_site_IDref
    JOIN dbo.tbl_rank ON dbo.tbl_rank.rank_ID = dbo.tbl_rate.rate_rank_IDref
    WHERE dbo.tbl_rate.rate_site_IDref = #FORM.site_ID#

    and rs_getlems:
    SELECT *
    FROM dbo.tbl_LEM, dbo.tbl_employee, dbo.tbl_site, dbo.tbl_rank
    WHERE dbo.tbl_employee.employee_ID = dbo.tbl_LEM.employee_IDREF
    AND dbo.tbl_LEM.site_IDREF = dbo.tbl_site.site_ID
    AND dbo.tbl_rank.rank_ID = dbo.tbl_employee.employee_rankREF
    AND dbo.tbl_LEM.LEM_date BETWEEN '#FORM.LEM_datefrom#' and '#FORM.LEM_dateto#'
    AND dbo.tbl_site.site_ID = #FORM.site_ID#
    AND dbo.tbl_LEM.LEM_posted = 'yes'
    ORDER BY dbo.tbl_LEM.LEM_date ASC

    I want to somehow join these 2 queries mentioned above.
    Here are the column names for all the tables I need to pull out.

    tbl_rate:
    rate_ID, rate_site_IDref, rate_rank_IDref, rate_dollar, rate_ot, rate_dt, rate_loa

    tbl_lem:
    LEM_ID, LEM_date, employee_IDREF, LEM_reg_hrs, LEM_ot_hrs, LEM_dt_hrs, LEM_tt_hrs, site_IDREF, LEM_LOA, LEM_Expenseinfo,LEM_workorder, LEM_posted, LEM_Expense, LEM_reg_rate, Lem_ot_rate, LEM_dt_rate, Lem_tt_rate, Lem_loa_rate

    tbl_rank:
    rank_ID, rank_name,

    tbl_employee:
    employee_ID, employee_name, employee_number, employee_rankREF

    tbl_site:
    site_ID, site_Name, site_JobNumber

    What I need to accomplish is: After navigating to a specific jobsite (#FORM.site_ID#) and specific date ( #FORM.LEM_date), the user chooses an employee from a drop down list and assigns hours (regular time, overtime, double time, etc) then the info gets posted to tbl_LEM. Which appears in an ongoing list on the same page. Each Iteration of rs_getLEMS needs to somehow have the rates from rs_getrates ( rate_dollar, rate_ot, rate_dt, rate_loa) in hidden input feilds ( LEM_reg_rate, Lem_ot_rate, LEM_dt_rate, Lem_tt_rate, Lem_loa_rate) so the user can "post" and update tbl_lems.

    Hopefully this makes it all clear...
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    If there a foreign key between tbl_LEM and tbl_rate?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    2
    Rep Power
    0
    No there is not.
    tbl_rate is a link table for tbl_site and tbl_rank.
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,263
    Rep Power
    968
    If there's no way to join the tables I'm not sure what you actually want to do. How can you merge this all into one query if there is no way to relate the two tables?

IMN logo majestic logo threadwatch logo seochat tools logo