I am trying to put together a query for an Oracle DB that will retrieve a list of tickets, along with any groups that have not registered an action against that ticket. The DB schema is similar to the following:

[tickets]---ticket_id---[actions]---group_id---[groups]

Expected output:

Ticket    Group
------    -----
111111    Development
111111    Testing
111111    Help Desk
222222    Development
222222    Help Desk
333333    Testing

I've tried

1. Left join from groups to actions
2. Left join from actions to groups
3. Filtering on actions where the group_id is not in the actions table

TBH, none of the queries that I've tried or the ideas that I've considered have even come anywhere close

CodePudding user response：

Ideally there should be tables containing all distinct tickets and groups. In the absence of this, we can use inline distinct queries. One way to do this uses a calendar table approach with left anti-join:

SELECT ti.Ticket, g."Group"
FROM (SELECT DISTINCT Ticket FROM yourTable) ti
CROSS JOIN (SELECT DISTINCT "Group" FROM yourTable) g
LEFT JOIN yourTable t
    ON t.Ticket = ti.Ticket AND
       t."Group" = g."Group"
WHERE
    t.Ticket IS NULL;

In the above, the cross join generates all combinations of tickets and groups. We then left anti-join this to your table, to retain all combinations which do not appear in the original table.