Joining issue

[clawr687]

Hi, I'm fairly new to this and have finally gotten myself stumped. I am really having difficulty with this one....

What I need to do is somehow combine 2 queries.

The first query is a many to many relationship using 3 tables (rs_getrate)

The second query is a simple one to many relationship joining 4 tables (rs_getlems)

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#

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'

AND dbo.tbl_employee.employee_isemployee = 'y'

ORDER BY dbo.tbl_LEM.LEM_date ASC

What I want to do is somehow do what <cfoutput group> does

                Group1

                     Item1

                     Item2

                Group2

                     Item1

                     Item2

The groups are from rs_getrank

The items in the group are rs_getlems

Is there anyway I can go about doing this? Is there a way to join these 2 queries?

Thanks in advance

[clawr687]

oops  what I meant to say was the groups are from the first query shown above

and the items are from the second query shown above

[BKBK]

You will have to tell us what column you wish to group by.

[Dan_Bracuk]

If there are fields to use, you can combine the two queries into one.  The fact that you have dbo.tbl_rank adn dbo.tbl_site in both queries suggests that it might be possible.  However, you have to know your database to be sure.

[clawr687]

Hi, Sorry for the really late repy...

I am still having problems with this

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...

[BKBK]

You raised this question in your other thead, "Filter my array output". We gave you suggestions there.