> When I use this report, the tickets before the add of this field are not > listed.
That's because your query just uses JOIN without specifying a JOIN type. According to SQL-92 syntax this is an implicit INNER JOIN, meaning that only records that actually have a match for tc.ticket = t.id are included. What you want is a LEFT JOIN (short for LEFT OUTER JOIN). That will still list records from the left table regardless of if there's a match. See http://en.wikipedia.org/wiki/Join_(SQL) for a better explanation of joins. On 6/4/07, diego gentoo <[EMAIL PROTECTED]> wrote: > Hi there! I have this report: > > SELECT p.value AS __color__, > id AS ticket, summary, component, version, milestone, t.type AS type, > (CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner, > time AS created, changetime AS _changetime, description AS _description, > reporter AS _reporter, lower(tc.value) AS __group__ > > FROM ticket t > LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority' > JOIN ticket_custom tc ON tc.ticket = t.id > WHERE status IN ('new', 'assigned', 'reopened') > ORDER BY tc.value, p.value , milestone, t.type, time > > which lists the tickets that have a filled department field selected from a > provided dropdown list (with a value or blank). When I use this report, the > tickets before the add of this field are not listed. I'd like to have all > together the tickets without a department (blank or not existing one) in a > category "Uncategorised", like the others. > > It is possible? Thanks. Diego. > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Trac Users" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/trac-users?hl=en -~----------~----~----~----~------~----~------~--~---
