问题描述:

I have two tables named tNetworkSocket and tPatchpanelPorts which I use to filter data.

I have data when I execute SELECT * FROM tPatchpanelPorts but, when SELECT * FROM tNetworkSocket there are no data. This is correct.

If I execute

SELECT Distinct HostID, HostName, HostTypeID, DomainName

FROM tHosts, tDomains, tPatchpanelPorts, tNetworkSocketPort

WHERE tHosts.DomainID=tDomains.DomainID

AND ( tPatchpanelPorts.ConnectedHostID = tHosts.HostID OR tNetworkSocketPort.ConnectedHostID = tHosts.HostID) And AccountID=1

I get no data, but if i remove tNetworkSocketPort from the query it looks like:

SELECT Distinct HostID, HostName, HostTypeID, DomainName

FROM tHosts, tDomains, tPatchpanelPorts

WHERE tHosts.DomainID=tDomains.DomainID

AND ( tPatchpanelPorts.ConnectedHostID = tHosts.HostID) And AccountID=1

I get data.

What am I missing

网友答案:

Re-Writing query with Explicit JOIN Syntax and for table I have used LEFT JOIN, this may help you

SELECT Distinct HostID, HostName, HostTypeID, DomainName 
FROM tHosts
INNER JOIN tDomains ON tHosts.DomainID=tDomains.DomainID 
INNER JOIN tPatchpanelPorts ON tPatchpanelPorts.ConnectedHostID = tHosts.HostID
LEFT JOIN tNetworkSocketPort ON tNetworkSocketPort.ConnectedHostID = tHosts.HostID
WHERE AccountID=1
网友答案:

Try it like this with a few joins:

SELECT Distinct HostID, HostName, HostTypeID, DomainName 
FROM tHosts 
INNER JOIN tDomains
ON tHosts.DomainID=tDomains.DomainID
LEFT JOIN tPatchpanelPorts
ON tPatchpanelPorts.ConnectedHostID = tHosts.HostID
LEFT JOIN tNetworkSocketPort 
ON tNetworkSocketPort.ConnectedHostID = tHosts.HostID 
WHERE AccountID=1
网友答案:

The out-dated comma-separated joins you are using are very hard to read. Your query translates to:

SELECT Distinct HostID, HostName, HostTypeID, DomainName 
FROM tPatchpanelPorts pp
CROSS JOIN tNetworkSocketPort nsp
JOIN tHosts h ON h.HostID IN (pp.ConnectedHostID, nsp.ConnectedHostID)
JOIN tDomains d on d.DomainID = h.DomainID 
WHERE AccountID = 1;

You cross join tPatchpanelPorts and tNetworkSocketPort to get all possible combinations. As one of the tables is empty, there are zero possible combinations. Then you join the other tables to this empty result set, which remains empty of course.

Moreover DISTINCT is very often a sign for a poorly written query. And really, you are joining four tables, when you only want data from two of them. The other tables belong in the WHERE clause.

I assume you want this:

SELECT h.HostID, h.HostName, h.HostTypeID, d.DomainName 
FROM tHosts h 
JOIN tDomains d ON d.DomainID = h.DomainID 
WHERE AccountID = 1
AND
( 
  h.HostID IN (SELECT ConnectedHostID FROM tPatchpanelPorts)
  OR
  h.HostID IN (SELECT ConnectedHostID FROM tNetworkSocketPort)
);

(I suppose that AccountID resides in either tHosts or tDomains. You should use a qualifier to make this clear.)

相关阅读:
Top