INNER JOIN, query to joins three tables needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

INNER JOIN, query to joins three tables needed

Author

Topic

specex
Starting Member

6 Posts

Posted - 2008-11-30 : 23:56:49

I need to join three tables, since i'm new to SQL so i need getting help from yours...

The following query create and joins the 1st two tables (PingLog, HostList). Three columns were created...(Host, RecordingDate, Status)

SELECT AllPing.Host, AllPing.RecordingDate, AllPing.Status FROM PingLog AS AllPing
INNER JOIN (SELECT Host, MAX(RecordingDate) AS LastRecordingDate FROM PingLog GROUP BY Host) LastPing
ON AllPing.Host=LastPing.Host AND AllPing.RecordingDate=LastPing.LastRecordingDate
INNER JOIN HostList ON HostList.Host=AllPing.Host
WHERE HostList.ShowInMonitor='Y' ORDER BY HostList.Host

Now i need join another table named SystemDetails which consist of column (RetailerID, SystemID, IPAddress). Here IPAddress will match the "Host" I've created earlier.

So the final column results i wish to populate is like...
Host | RecordingDate | Status | RetailerID | SystemID

Hope experts can help me on this, thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 00:00:41

[code]SELECT AllPing.Host, AllPing.RecordingDate, AllPing.Status,
sd.RetailerID,sd.SystemID
FROM PingLog AS AllPing
INNER JOIN (SELECT Host, MAX(RecordingDate) AS LastRecordingDate FROM PingLog GROUP BY Host) LastPing
ON AllPing.Host=LastPing.Host AND AllPing.RecordingDate=LastPing.LastRecordingDate
INNER JOIN HostList ON HostList.Host=AllPing.Host
INNER JOIN SystemDetails sd ON sd.IPAddress=AllPing.Host
WHERE HostList.ShowInMonitor='Y' ORDER BY HostList.Host[/code]

specex
Starting Member

6 Posts

Posted - 2008-12-01 : 01:08:41

It's works, now I've learned it, thanks visakh16...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 01:11:04

No problem...You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources