Joining issue
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
AdChoices