You've made a join twice on the ticket_custom table by including it in your
FROM clause and by the JOIN.
Try this:
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, tc.value AS department
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 p.value, milestone, t.type, time
Regards,
Casper
On 5/30/07, diego gentoo <[EMAIL PROTECTED]> wrote:
>
> Hi there, I've this query:
>
> 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, ticket_custom.value AS department
>
> FROM ticket_custom, 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 p.value, milestone, t.type, time
>
> It should simply give back the open tickets with the Department (a custom
> field), but in the output I have all the tickets repeated severals times
> with a different department field (ticket n.1 with Department:X AND ticket
> n.1 with Department:Y):
>
> I cannot understand...
>
> Thanks. Diego.
>
>
> >
>
--
/Casper
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
