get data from differnt table?
I have been looking around all over google and I just can find a good example of a join query
for what i want to do.
lets say you have two tables i created for example, from same server.
create table table_a (employee_id int, stars int);
insert into table_a values (1, 1);
insert into table_a values (1, 0);
insert into table_a values (1, 1);
insert into table_a values (1, 0);
insert into table_a values (2, 1);
insert into table_a values (3, 1);
insert into table_a values (2, 1);
insert into table_a values (2, 1);
create table table_b (employee_id_number int, dept_name varchar(25));
insert into table_b values (1, 'deptone');
insert into table_b values (2, 'depttwo');
insert into table_b values (3, 'deptthree');
insert into table_b values (4, 'deptfour');
I want to be able to ouput the max which i have been able to do with this below code:(this doesnt use the tables above)
<cfquery datasource="Intranet" name="getMaxstars">
select submitterdept, sum((rating1+rating2+rating3+rating4+rating5)/5)/count(1) average_rating
from CSEReduxResponses
group by submitterdept
order by 2 desc
</cfquery>
<cfset average_rating_max = 0>
<cfoutput query="getMaxstars">
<cfif average_rating GTE average_rating_max>
<cfset average_rating_max = average_rating>
</cfif>
</cfoutput>
<cfoutput>#average_rating_max#</cfoutput>
But i want to use the other table so i can output the dept_name that matches the employee_id MAX number.
Can anyone help me the best way to do this, i feel really lost,thanks.
1
Reply
AdChoices