MySQL multiple inner joins

Databases
1

Hi there,

I am trying to work out some MySQL inner join commands and wondered if anybody might know how I can achieve the following.

I need to make a MySQL query that lists my running scripts table and gets the name of the script from another table.

runningscripts Table Structure:

I want to output the following information: Script Name | Computer Name | Client Name | Started Running Time
where ‘runningscripts.running’ row = 1

The “Script Name” is stored in the ‘lt_scripts.name’ table
The “Computer Name” is stored in the ‘computers.name’ table
The “Client Name” is stored in the ‘clients.name’ table
The “Started Running Time” is stored in the ‘runningscripts.start’ table

So far, I have:

 SELECT ScriptName, ComputerID, ClientID, Executed, Step FROM runningscripts T1 INNER JOIN lt_scripts T2 ON T1.ScriptID = T2.ScriptID WHERE Running = 1

This returns The Script Name, Computer ID and Client ID Executed and Step as expected.
Result:

I know need am trying to figure out how to do multiple inner joins on that statement to get the Client Name and Computer Name by adding the following two joins into the query above to make one long query.

SELECT Name FROM runningscripts T1 INNER JOIN computers T2 ON T1.ClientID = T2.ClientID

SELECT Name FROM runningscripts T1 INNER JOIN computers T2 ON T1.ComputerID = T2.ComputerID

That’s hard to explain, hopefully you get what I mean.

2

Hi there,

I have three sql queries with inner joins that I need to combine into one query.

SELECT ScriptName, ComputerID, ClientID, Executed, Step FROM runningscripts T1 INNER JOIN lt_scripts T2 ON T1.ScriptID = T2.ScriptID WHERE Running = 1
SELECT Name FROM runningscripts T1 INNER JOIN clients T2 ON T1.ClientID = T2.ClientID
SELECT Name FROM runningscripts T1 INNER JOIN computers T2 ON T1.ComputerID = T2.ComputerID

The first result returns a Computer ID and Client ID field that I then need the other two queries to populate the name so my final results show

ScriptName | Computer Name| Client Name | Executed | Step

3 
SELECT lt.scriptname
     , rs.computerid
     , rs.clientid
     , rs.executed
     , rs.step 
     , cl.name AS client
     , co.name AS computer
  FROM runningscripts AS rs 
INNER 
  JOIN lt_scripts AS lt 
    ON lt.scriptid = rs.scriptid 
INNER 
  JOIN clients AS cl 
    ON cl.ClientID = rs.ClientID    
INNER 
  JOIN computers AS co 
    ON co.ComputerID = rs.ComputerID    
 WHERE rs.running = 1