CODEWITHSUNDEEP
mysqlsql
bilal shakir
bilal shakir

Reputation: 47

MySQL Subquery Optimization with shared subquery

From these tables I have written this subquery and its giving results as per requirements.

Needs expert guidence to improve this query or if we can also be able to use join for these tables.

Query:

select ps,st from pac where con in (select 
config from config where logi in 
( select id from logicalnode where physi 
in (select id from ysicalnode where mas =11)));

Upvotes: 0

Views: 142

Answers (5)

sabhari karthik
sabhari karthik

Reputation: 1371

Try this

    select pa,sta 
    from 
   pack p
    INNER JOIN
  confi c
    ON
    p.confi = c.idco
    INNER JOIN
   logice l
    ON
    c.logic = l.id
    INNER JOIN
  physiode pn
    ON
    l.physicalnodeid  = pn.id
    WHERE macaddress =123

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try below - using JOIN

select payloadstr,starttime 
    from packetdb.packet a inner join packetdb.configuration b on a.configid=b.idconfig
    inner join packetdb.logicalnode c on logicalnodeid=c.id
    inner join packetdb.physicalnode d on physicalnodeid=d.id
    where macaddress =117769729

Upvotes: 0

KsV
KsV

Reputation: 46

SELECT
    payloadstr
    ,starttime
FROM packetdb.packet
INNER JOIN packetdb.configuration
    ON packetdb.packet.configid = packetdb.configuration.idconfig
INNER JOIN packetdb.logicalnode
    ON packetdb.configuration.idconfig = packetdb.logicalnode.id
INNER JOIN packetdb.physicalnode
    ON packetdb.logicalnode.physicalnodeid = packetdb.physicalnode.id and packetdb.physicalnode.macaddress=117769729

Upvotes: 1

Rima
Rima

Reputation: 1455

Using Left join

select payloadstr,starttime 
from packet 
left join Configuration on Configuration.IDconfig = packet.configID
left join logicalnode on logicalnode.ID = Configuration.logicalnodeid
left join physicalnode on physicalnode.ID = logicalnode.physicalnodeid
 where macaddress =117769729

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37367

You could try using exists:

select payloadstr,starttime from packetdb.packet p
where exists(select 1 from packetdb.configuration c
             where p.configid = id
             and exists(select 1 from packetdb.logicalnode l
                          where c.logicalnodeid = id
                          and exists(select 1 from packetdb.physicalnode
                                     where macaddress = 117769729
                                     and l.physicalnodeid = id)

Upvotes: 0

Related Questions