1. Home
  2. Forums Index
  3. Server Side / Databases 2:02 am May 21, 2026

Forum Moderators: open

Message Too Old, No Replies

Nested query help

         

oxidetones

1:35 pm on Jun 23, 2010 (gmt 0)

10+ Year Member



Hi there,

I have a query that I need to edit, and I hope to approach it with a nested query, but I cant quite work it out. Its for a ticketing system, and the query uses a left join to join 2 tables, 1 with the ticket details, and 1 with department details.

The ticket table has an ID column (each ticket is unique) but also a parent column. The parent column can be 0, or it can be a number, which is then identical to the ID of the parent ticket.

I have the following query right now, which is used to populate a table with ticket details 

SELECT 
qt_tickets.`ID`, qt_users.`fname`, qt_users.`lname`, qt_users.`email`, qt_tickets.`assigned_to`,qt_users.`receive`, qt_tickets.`uploads`, qt_tickets.`ADE`, qt_tickets.`created`, qt_tickets.`Grund`,qt_tickets.`storno`, qt_tickets.`channel`, qt_tickets.`modified`,qt_tickets.`MC`,qt_tickets.`Region`, qt_tickets.`unkosten`, qt_tickets.`reviewdate`, qt_tickets.`priority`, qt_tickets.`status`, qt_departments.`name`, qt_tickets.`DEPARTMENT_ID`, qt_tickets.`parent`
FROM qt_users, qt_tickets 
LEFT JOIN qt_departments ON qt_tickets.`DEPARTMENT_ID`=qt_departments.`ID` 
WHERE qt_tickets.by=qt_users.ID 
ORDER BY qt_tickets.`ID` DESC


What I want to add is the count of children (if any) each ticket has. qt_tickets.`ID` and qt_tickets.`parent` are the relevant columns.

Any help would be appreciated.

Thanks, Adam

DWarp9

10:44 pm on Jun 26, 2010 (gmt 0)

10+ Year Member



I think you're on the right track. Something like:
SELECT parents.count,
qt_tickets.`ID`, qt_users.`fname`, qt_users.`lname`, qt_users.`email`, qt_tickets.`assigned_to`,qt_users.`receive`, qt_tickets.`uploads`, qt_tickets.`ADE`, qt_tickets.`created`, qt_tickets.`Grund`,qt_tickets.`storno`, qt_tickets.`channel`, qt_tickets.`modified`,qt_tickets.`MC`,qt_tickets.`Region`, qt_tickets.`unkosten`, qt_tickets.`reviewdate`, qt_tickets.`priority`, qt_tickets.`status`, qt_departments.`name`, qt_tickets.`DEPARTMENT_ID`, qt_tickets.`parent`
FROM qt_users, qt_tickets
LEFT JOIN qt_departments ON qt_tickets.`DEPARTMENT_ID`=qt_departments.`ID`
Left Join (Select parent_id, sum(1) as count From qt_tickets Where parent_id > 0 Group By parent_id) as parents On qt_tickets.parent_id = parents.parent_id
WHERE qt_tickets.by=qt_users.ID
ORDER BY qt_tickets.`ID` DESC
I haven't tested it, but I hope you get the idea. Remember to substitute the column name parent_id for the actual column name.

Hope this helps.
-Peter

Edit: typo.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / Databases 2:02 am May 21, 2026