1 Reply Latest reply on Apr 11, 2014 11:34 AM by WolfShade

    get data from differnt table?

    no_name_123

      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. Re: get data from differnt table?
          WolfShade Level 4

          LEFT OUTER JOIN will get all data from table A and all related data from table B.

           

          A standard JOIN will only get data that is related between tables.

           

          <cfquery datasource="dsnName" name="sampleLOJ">

          SELECT a.columnA, a.columnB, b.columnA, b.columnB

           

          FROM tableA a LEFT OUTER JOIN tableB b ON b.colunnA = a.columnA

          </cfquery>

          This will get all data from tableA and all related data from tableB, where columnA in both are identical.

           

          <cfquery datasource="dsnName" name="sampleJOIN">

          SELECT a.columnA, a.columnB, b.columnA, b.columnB

           

          FROM tableA a JOIN tableB b ON b.colunnA = a.columnA

          </cfquery>

          This will get only the data from tableA and related tableB data that have identical columnA entries.

           

          ^_^