[SOLVED]triple join and specific listing needed

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

[SOLVED]triple join and specific listing needed

Post by ryuuka »

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

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
haven't started to put table 3 in it yet. difficult enough to put 2 together
Last edited by ryuuka on Thu Sep 14, 2006 1:13 am, edited 1 time in total.
asgerhallas
Forum Commoner
Posts: 80
Joined: Tue Mar 14, 2006 11:11 am
Location: Århus, Denmark

Post by asgerhallas »

I'm not sure I get it... why:

Code: Select all

b.LastCheck < c.LastCheck
you want columns from both table 2 and 3, but why do you compare them?

/Asger
asgerhallas
Forum Commoner
Posts: 80
Joined: Tue Mar 14, 2006 11:11 am
Location: Århus, Denmark

Post by asgerhallas »

I would say something like (forgive me if i'm mistaken this is not tested):

Code: Select all

SELECT a.merk, a.type, a.date, a.jaar, b.ServerName, b.Status, b.LastCheck
FROM computers a 
INNER JOIN ServerPingStatus b 
ON a.computernaam = b.ServerName
ORDER BY b.LastCheck DESC
GROUP BY b.ServerName
/Asger
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post by ryuuka »

thanks for the advice people but i solved the problem in a different manner

just 2 querys works just as well
thanks for the advice anyway
Post Reply