[SOLVED]triple join and specific listing needed
Posted: Wed Sep 13, 2006 6:23 am
hello again
i got 3 tables i need to join and make a total list wich is proven to be very difficult
using mssql for this one
table 1.......................table 2..............table 3
computernaam..........Servername......servername
merk..........................Status...............servicenaam
type...........................LastCheck.........Status
serienummer.......................................lastcheck
cpu
memory
NIC
MAC-adres
OS
OEM
SP
Date
Jaar
so the problem here is:
i want the
table 1 fields merk, type, Date, Jaar
table 2 field servernaam and status
table 3 status
to be displayed. now normaly this would not be a problem but the second table
says if a server online or not meaning it gives a new entry every 5 min with the time and date of the last check
i need only the latest of these per server.
this also goes for the third table
also the computernaam, Servername and servername need to be the same.
and i havent yet been able to get 1 row per server instead of, say 200
so basicly i want servernaam, merk, Date, Jaar, Status (table 2) and Status (table 3)
so be displayed and i want it to be only 1 row per servername
this is what i got (not even close):
table 1 = a, table 2 = b, table 3 = c
table 1 = computers
table 2 = ServerPingStatus
table 3 = services_status
haven't started to put table 3 in it yet. difficult enough to put 2 together
i got 3 tables i need to join and make a total list wich is proven to be very difficult
using mssql for this one
table 1.......................table 2..............table 3
computernaam..........Servername......servername
merk..........................Status...............servicenaam
type...........................LastCheck.........Status
serienummer.......................................lastcheck
cpu
memory
NIC
MAC-adres
OS
OEM
SP
Date
Jaar
so the problem here is:
i want the
table 1 fields merk, type, Date, Jaar
table 2 field servernaam and status
table 3 status
to be displayed. now normaly this would not be a problem but the second table
says if a server online or not meaning it gives a new entry every 5 min with the time and date of the last check
i need only the latest of these per server.
this also goes for the third table
also the computernaam, Servername and servername need to be the same.
and i havent yet been able to get 1 row per server instead of, say 200
so basicly i want servernaam, merk, Date, Jaar, Status (table 2) and Status (table 3)
so be displayed and i want it to be only 1 row per servername
this is what i got (not even close):
table 1 = a, table 2 = b, table 3 = c
table 1 = computers
table 2 = ServerPingStatus
table 3 = services_status
Code: Select all
SELECT b.ServerName, b.Status, b.LastCheck, a.merk, a.type, a.date, a.jaar
FROM ServerPingStatus LEFT OUTER JOIN
computers ON b.ServerName = a.computernaam LEFT OUTER JOIN
ServerPingStatus c ON a.computernaam = c.ServerName AND
b.LastCheck < c.LastCheck
WHERE (c.LastCheck IS NULL)
GROUP BY ServerName, LastCheck, Status