How to filter duplicate records from a query

How to filter duplicate records from a query

I've got a fairly complex query that uses a bunch of joins.  The goal of the query is for a given MachineId, return all network communications where the source or destination is that MachineId or the MachineGroupId of any group that MachineId is a member of.  The problem is that when a MachineGroup is the source or destination of traffic, I'm getting a result back for each member of the group rather than just the group itself.  

I use an IF widget to display the Machine Name or Machine Group Name depending on whether the source or destination field contains a null MachineId.

So my query results look like for MachineId 1:

Source                        Destination                 (Destination Group MachineId -- not displayed, but part of the query)  (Source Group MachineId -- not displayed)
MachineId=1              MachineId=10             0                                                                                                                          0                                 
MachineId=1              MachineGroupId2      MachineId=3                                                                                                      0
MachineId=1              MachineGroupId2      MachineId=4                                                                                                      0
MachineGroupId=3   MachineId=9               0                                                                                                                           MachineId=1
MachineGroupId=3   MachineId=9               0                                                                                                                           MachineId=2

Since I only want to display the Destination MachineGroupId2 and Source MachineGroupId=3 once, how do I filter out records where the Source and Destination are duplicates?  I see some duplicate list logic in the delete actions that get created when I drag an entity onto a web page, but I don't know how to apply it to a query.  How do I construct the action to delete duplicate records and where do I place it on the flow?

Here is the query if the problem can be solved by changing the joins:

Hi Craig,

You can add a for each after the query that remove duplicates, but if that list becomes to big, it will become performance Killer, so other option that you hacve is create a structure using only the output that you need and make a advance query with a distinct on it. this will make your output unique and if you use in the struct only the fields that you need you can get almost the same performance impact as you get with the simple query.

I hope that this helps,
Carlos Rocha
Hi Craig,

The query looks complex enough to require a Advanced Query. Unfortunatly I don't think even a simple Distinct will solve your problem.
If I understood your problem correctly you would need something like:

Select Disctinct {table1_that_you_want_to_display}.*, {table2_that_you_want_to_display}.*, ... From (
 Select for Direct Source
  Select for Direct Destination
 Select for Group Source
 Select for Group Destination

Also it looks like your current query is returning wrong results because of the groups joins (the last line on your example doesn't look like belonging to MachineId=1).

João Rosado