Subject | Re: [firebird-support] select parent based on multiple child conditions |
---|---|

Author | setysvar |

Post date | 2015-12-19T10:14:23Z |

Here's two alternatives for you, they should give the same result

(though provide different options for modifications, see the comments

inside the SQL), pick the one you prefer. Both differ from your SQL in

that rows are also returned when there are no routes, whereas your SQL

would require at least one route to get a result.

Alternative 1)

select distinct n.NODE

from NODES n

left join ROUTES r on n.ID = r.NODE_ID --remove LEFT from this touple

if you don't want to return NODES with no route.

left join ROUTES r2 on n.ID = r2.NODE_ID and r.ID <> r2.ID

where ((r.ROUTE_INDEX in (1, 2)

and (r.NETCON>5 or r.QUALITY>3))

or r2.ID is null

Alternative 2)

select distinct NODE --remove distinct if you want one row for each

record in the NODES table, even when they have the same value for NODE.

from NODES n

where exists(select * from routes r

where n.ID = r.NODE_ID

and r.ROUTE_INDEX in (1, 2)

and r.NETCON>5 or r.QUALITY>3)

or not exists(select * from routes r

join routes r2 on r.NODE_ID = r2.NODE_ID

and r.ID < r2.ID

where n.ID = r.NODE_ID)

If you prefer alternative 2 and want only touples with exactly one

route, the NOT EXISTS can be changed to:

or exists(select * from routes r

left join routes r2 on r.NODE_ID = r2.NODE_ID

and r.ID < r2.ID

where n.ID = r.NODE_ID

and r2.ID is null)

HTH,

Set

(though provide different options for modifications, see the comments

inside the SQL), pick the one you prefer. Both differ from your SQL in

that rows are also returned when there are no routes, whereas your SQL

would require at least one route to get a result.

Alternative 1)

select distinct n.NODE

from NODES n

left join ROUTES r on n.ID = r.NODE_ID --remove LEFT from this touple

if you don't want to return NODES with no route.

left join ROUTES r2 on n.ID = r2.NODE_ID and r.ID <> r2.ID

where ((r.ROUTE_INDEX in (1, 2)

and (r.NETCON>5 or r.QUALITY>3))

or r2.ID is null

Alternative 2)

select distinct NODE --remove distinct if you want one row for each

record in the NODES table, even when they have the same value for NODE.

from NODES n

where exists(select * from routes r

where n.ID = r.NODE_ID

and r.ROUTE_INDEX in (1, 2)

and r.NETCON>5 or r.QUALITY>3)

or not exists(select * from routes r

join routes r2 on r.NODE_ID = r2.NODE_ID

and r.ID < r2.ID

where n.ID = r.NODE_ID)

If you prefer alternative 2 and want only touples with exactly one

route, the NOT EXISTS can be changed to:

or exists(select * from routes r

left join routes r2 on r.NODE_ID = r2.NODE_ID

and r.ID < r2.ID

where n.ID = r.NODE_ID

and r2.ID is null)

HTH,

Set