-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Am 22.08.2012 04:33, schrieb Albert Chin: > Hi. I need help designing a report to show all tickets where the user > is the owner, reporter, or tickets they have not created but tickets > they have left a comment on. It's the last part that's tricky. I can > accomplish the first two with: > SELECT p.value AS __color__, > t.milestone AS __group__, > id AS ticket, t.type AS type, owner, reporter, > time as created, changetime AS modified > FROM ticket t, enum p > WHERE owner = 'gary' OR reporter = 'gary' > ORDER BY t.milestone, p.value, t.type, t.time
No, this won't work as you might expect. You'll need to JOIN tables, or you'll end up with a lot of duplicated rows otherwise (look for 'cartesian product', if you don't understand that). This is one is a more appropriate report SQL statement, at least to show "(current) user is owner or reporter" SELECT p.value AS __color__, t.milestone AS __group__, id AS ticket, t.type AS type, owner, reporter, time as created, changetime AS modified FROM ticket t LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority' WHERE owner = $USER OR reporter = $USER ORDER BY t.milestone, p.value, t.type, t.time Note, that I replaced you hard-coded username with one of the available variables to dynamically show results depending on logged in user, because you might rather want that instead of one-report-per-user. About the comment, this is in another table, ticket_change. I'm just looking at it. Will keep you updated, if I see a way to do this too. Sincerely, Steffen Hoffmann -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAlA+j7EACgkQ31DJeiZFuHcJkACg0T48GeonwUf9k6Sy6oet0ew3 jrsAnA+oQvOCSElJR6L9r4uUQPplzfmm =Ofh/ -----END PGP SIGNATURE----- -- You received this message because you are subscribed to the Google Groups "Trac Users" group. To post to this group, send email to trac-users@googlegroups.com. To unsubscribe from this group, send email to trac-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/trac-users?hl=en.
