How can I combine these to tables?

no_name_123 wrote: I have a query that i get the MAX number of "stars"...    I also have a different table EMPLOYEE. Table EMPLOYEE also comes from a different datasource="phonelist". Where in this table I have the employees first_name and last_name columns , they share the same column emp_id. How can I output the employee first_name and last_name using the other table. What I eventually I want to do it output: max: john doe - stars = 4

The simple, 'dumb' way might just hack it. I was thinking of a query of a query, something like

<cfquery datasource="Intranet" name="getMaxstars">

    SELECT   TOP (1) WITH TIES emp_id, SUM(execoffice_status) AS total_max

    FROM     CSEReduxResponses

    GROUP BY emp_id

    ORDER BY total_max DESC

</cfquery >

<cfquery datasource="phonelist" name="getEmployees">

    SELECT first_name, last_name, emp_id

    FROM employee

</cfquery>

<!--- Query of a query join --->

<cfquery name="getEmployeeStars" dbtype="query">

    SELECT getEmployees.first_name + ' ' + getEmployees.last_name AS full_name, getMaxstars.total_max AS stars

    FROM getMaxstars, getEmployees

    WHERE getMaxstars.emp_id = getEmployees.emp_id

</cfquery>

<cfdump var="#getEmployeeStars#">

We need more details about the two datasources.  Are they on the same server?  Do they use the same SQL Login (or does either of them use a SQL Login that has access to both databases)?  If Yes to both, then you can do a join to get the result you want.  But if they are on different servers, or if there is not a common logon, then no dice - you'll need to do the first query to get the max star, and then do the second query for the user info based on the ID from the first query.  In the first query you will need to include the emp_ID in the SELECT list and the GROUP BY list, so that it shows up in the resultset.  That would be a good idea in any event, just in case there are 2 employees with the same name - the first query as written would combine their data.

-reed

I believe that as long as the account accessing the first table has permissions to the second table, then all you need to do is a LEFT OUTER JOIN, and alias both tables.

SELECT TOP (1) WITH TIES e.employee, SUM(e.execoffice_status) as 'total_max', emp.first_name, emp.last_name

FROM e.CSEReduxResponses LEFT OUTER JOIN emp.EMPLOYEE on emp.[unique_id] = e.[same unique_ID]

GROUP BY e.employee

ORDER BY 'total_max' DESC

Obviously, not tested.  You might have to tweak it, a bit.  But that's the gist of it.

Actually, now that I think of it.. the query can only access on datasource.. I _think_.. so, you might have to loop through the first query and query the other datasource with data from the first query.

HTH,

^_^

UPDATE:  You _might_ be able to do it in a Stored Procedure, but then that would require special settings on the database, for it.