homepage Welcome to WebmasterWorld Guest from 54.211.113.223
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Nested query help
oxidetones

5+ Year Member



 
Msg#: 4157603 posted 1:35 pm on Jun 23, 2010 (gmt 0)

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

5+ Year Member



 
Msg#: 4157603 posted 10:44 pm on Jun 26, 2010 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved