How can I combine these to tables?

Report · Apr 10, 2014

I have a query that i get the MAX number of "stars"

    <cfquery datasource="Intranet" name="getMaxstars">
    SELECT   TOP (1) WITH TIES employee, SUM(execoffice_status) AS 'total_max'
    FROM     CSEReduxResponses
    GROUP BY employee
    ORDER BY 'total_max' DESC
    </cfquery >

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

Report · Apr 10, 2014

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.

Report · Apr 10, 2014

are you saying use a using a left join?

Report · Apr 10, 2014

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

Report · Apr 10, 2014

yes to the the question

Report · Apr 10, 2014

Good, then you should be able to do something like the following. You said that you were only looking for a single line of results, to find the top star - wo why do you have WITH TIES in the SELECT list?

Try working with this SQL as a model. it might need some tweaking. You'll need to substitute the name of the database and schema for the Employee table in the appropriate places. All this really is is a cross-join without the JOIN verb, to make it a little simpler to read. It looks strange to have both the employee and the firstname/lastname in the GROUP BY, but that's what you need to do if you want the first and last names to be part of the resultset. If this gets too hairy to work with, then just follow my second suggestion in the earlier posting, and break it into two CFQUERYs - one to find the star, the other to get the info. As I said above, this is only going to work if the SQL user defined for the getMaxstars datasource also has access to the database for the Employees table, and if both databases are on the same instance of SQL Server.

SELECT TOP (1)

CSEReduxResponses.employee, SUM(CSEReduxResponses.execoffice_status) AS 'total_max',

otherdb..employee.first_name,otherdb..employee.last_name

WHERE otherdb..CSEReduxResponses.emp_otherdb..IDemployee.emp_ID

FROM CSEReduxResponses, otherdb..employee

GROUP BY CSEReduxResponses.employee,otherdb..employee.first_name,otherdb..employee.last_name

ORDER BY SUM(CSEReduxResponses.execoffice_status) DESC

</cfquery >

Report · Apr 10, 2014

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

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 >

SELECT first_name, last_name, emp_id

FROM employee

</cfquery>

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>

Report · Apr 10, 2014

@bkbk

i have try what you have said but i keep getting error

Query Of Queries syntax error.

Encountered ". Incorrect Select List, Incorrect select column, getEmployees.emp_namefirst cannot be followed by '+

ps: the name_first is actually named emp_namefirst in my table

i also have created another query that does the same thing the first one did

<cfquery datasource="Intranet" name="getMaxstars">
select employee, sum(execoffice_status) as num_stars
  from csereduxresponses
 group by employee
having sum(execoffice_status) = (select max(num_stars)
                       from (select employee, sum(execoffice_status) as num_stars
                               from csereduxresponses
                              group by employee) x)
</cfquery>

this works, but i would still have to get the names(first and last ) from the other table

Report · Apr 11, 2014

Could you show us the select list you used? It seems like it contains a redundant quotation mark.

Adobe Community

How can I combine these to tables?