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)));
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
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)
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
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
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
