sql - Best way to select record with out IN operator in MYSQL -


i have table called sub_router below sample data , 2 columns

associated_router_l1,associated_router_l2 both composite primary key.

i want select l2 ronter id given l1 routerid example if gave 2001 result 2005,2006,2002,2007,2003,2008

my select sql is

query - 1(with 1 level)

select s1.associated_router_l2  sub_router s1  s1.associated_router_l1 in (select s2.associated_router_l2                                    sub_router s2                                    s2.associated_router_l1 = 2001) 

query - 2(with 2 level)

select s1.associated_router_l2  sub_router s1  s1.associated_router_l1 in (select s2.associated_router_l2                                    sub_router s2                                    s2.associated_router_l1 in (select s3.associated_router_l2                                    sub_router s3                                    s3.associated_router_l1 = 2001)) 

query-2 using inner join

select s1.associated_router_l2 sub_router s1 inner join sub_router s2 on s1.associated_router_l1 = s2.associated_router_l2 inner join sub_router s3 on s2.associated_router_l1 = s3.associated_router_l2 s1.associated_router_l1 = 2001 or s2.associated_router_l1 = 2001 or s3.associated_router_l1 = 2001

or operation again creating performace

result coming fine, better approach avoid in,or operator.

table name: sub_router
column name: associated_router_l1,associated_router_l2 both composite primary keys data attached

associated_router_l1    associated_router_l2                 2000    2001                 2000    2002                 2000    2003                 2000    2004                 2001    2005                 2001    2006                 2001    2002                 2002    2007                 2002    2003                 2002    2008 

the following sql might help:

select distinct concat(s2.associated_router_l1, '-->', s2.associated_router_l2) branch sub_router s1, sub_router s2     s1.associated_router_l1 = 2001     , (s1.associated_router_l2 = s2.associated_router_l1 or 2001 = s2.associated_router_l1) 

if want go 1 lever deeper, add table, expand clause, , modify select fields:

select distinct concat(s3.associated_router_l1, '-->', s3.associated_router_l2) branch sub_router s1, sub_router s2, sub_router s3     s1.associated_router_l1 = 2001     , (s1.associated_router_l2 = s2.associated_router_l1 or 2001 = s2.associated_router_l1)     , (s2.associated_router_l2 = s3.associated_router_l1 or s1.associated_router_l2 = s3.associated_router_l1 or 2001 = s3.associated_router_l1) 

Comments

Popular posts from this blog

matlab - "Contour not rendered for non-finite ZData" -

delphi - Indy UDP Read Contents of Adata -

javascript - Any ideas when Firefox is likely to implement lengthAdjust and textLength? -