SQLTeam.com | Weblogs | Forums

Filter out some data from a table based on inner conditions

Hi,
I m looking to exclude some rows from a table based on conditions within the same table.
here is an extract of few rows of my table :

Group Link Source destination
1 L_4 port 1 port 2
1 L_4 wrong wrong
1 L_5 port 91 port79
1 L_5 wrong wrong
1 L_5 port55 port80
1 L_7 port13 port14
1 L_8 port16 port28
1 L_6 port55 port11

The rules are as follow:

  • For each group, link to exclude rows where source or destination has the value wrong when there is at least one row with source and destination value different than "wrong" (case of Group 1 Link L_4)
  • For each group, link where more than one row with source and destination values different than "wrong" and source or destination of one of these rows exists in another group, link which has only one dataset ( Example Group 1, Link L5 has 2 connections port 91/port79 and port55/port80 but the last connection should be excluded because the port 55 exist in a unique connection of Group 1, Link L6 which is port55/port11.

the output would be something like that :

Group Link Source destination
1 L_4 port 1 port 2
1 L_5 port 91 port79
1 L_7 port13 port14
1 L_8 port16 port28
1 L_6 port55 port11

thank you in advance for your help