CODEWITHSUNDEEP
mysqlsqlcoldfusioncfquery
Brian Fleishman
Brian Fleishman

Reputation: 1257

Colfusion Query results based on query results from a related table

I am querying a table for all the available records. The results have a primary key called ticket_id

 <cfquery name="get_all_active_tickets">
  SELECT *
  FROM service_ticket
  where technician_id != <CFQUERYPARAM Value="#techID#"> AND technician_name != "" 
</cfquery>

The query returns 42 unique records, each record has a unique value for the column ticket_id.

I then want to query another another table for all records that also contain the primary key ticket_id and SUM a column in those results called service_qty for those related records. My goal is to add up all the values of service_qty for records that contains the same ticket_id value, and compare those values.

I am trying to do it like this:

<cfset IDs = valueList(get_all_active_tickets.ticket_id) >
<cfquery name="high_hours_tickets">
    select SUM(service_qty) as total, ticket_id
    from service_ticket_detail
    where ticket_id in (<cfqueryparam cfsqltype="cf_sql_integer" value="#IDs#" list="true">)
</cfquery>

However the results are not as expected. The record count for the query "high_hours_tickets" is only 1 and it looks like it is totaling the column 'service_qty' for all the records that are returned, not just the records that have the same ticket_id value.

How can I fix this? Thank you.

A solution that works, thanks to Ageax's contributions below:

<cfset IDs = valueList(get_all_active_tickets.ticket_id) >
<cfquery name="high_hours_tickets" datasource="#datasource#">        
    SELECT t.ticket_id, SUM( d.service_qty) AS totalQty
    FROM   service_ticket t 
    INNER JOIN service_ticket_detail d ON d.ticket_id = t.ticket_id
    WHERE   t.technician_id <> <cfqueryparam value="#techID#" cfsqltype="cf_sql_integer"> 
    AND     t.technician_name <> "" 
    GROUP BY t.ticket_id
    HAVING  SUM( d.service_qty) > 2
</cfquery>

<cfoutput><cfset lstIDs = ValueList(high_hours_tickets.ticket_id) /></cfoutput>

<cfquery name="flagged_tickets" datasource="#datasource#">
    select * 
    from service_ticket
    where ticket_id IN ( 
        <cfqueryparam 
            value="#lstIDs#" 
            cfsqltype="CF_SQL_INTEGER"
            list="yes" 
            /> 
    )
</cfquery>

Upvotes: 1

Views: 70

Answers (1)

SOS
SOS

Reputation: 6550

Update:

Since there was some confusion about the table structure and desired results, I put together a SQL Fiddle based on your description. It's a great tool for sharing schemas and sample data, and is helpful in avoiding a lot of back forth with query issues :-) I don't know which version of MySQL you're using, so I'll assume an older version for best compatibility.

If I'm understanding correctly, the goal is to display all service_ticket records, having an overall service_qty > 2. To do that, use a subquery to SUM the service_qty values, by ticket_id, and drop any that are <= 2. Then join those results back to the main ticket table to get the details (name, address, etc...).

SQL Fiddle

Query:

SELECT t.ticket_id
       , t.address
       , t.name
       -- ... additional columns
       , total.qty
FROM   service_ticket t INNER JOIN 
          (
             SELECT   ticket_id
                     , SUM( service_qty) AS Qty
             FROM   service_ticket_detail 
             GROUP BY ticket_id
             HAVING  Qty > 2
          ) 
          total ON total.ticket_id = t.ticket_id
WHERE   t.technician_id <> <cfqueryparam value="#techID#" cfsqltype="cf_sql_integer"> 
AND     t.technician_name <> ''

Original:

Forget about the separate queries. This is a job for a JOIN. They're designed for just this purpose.

SELECT t.ticket_id, SUM( d.service_qty) AS totalQty
FROM   service_ticket t 
        INNER JOIN service_ticket_detail d ON d.ticket_id = t.ticket_id
WHERE   t.technician_id <> <cfqueryparam value="#techID#" cfsqltype="cf_sql_integer"> 
AND     t.technician_name <> ''
GROUP BY t.ticket_id
HAVING  SUM( d.service_qty) > 2

IMO, JOIN's are indispensable. If you're unfamiliar with them, I'd strongly recommend reading a few introductory tutorials on JOIN's. You'll be glad you did.

Upvotes: 3

Related Questions