SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Mar 2014
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MySQL multiple inner joins

    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: runningscripts.png

    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:
    Code:
     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: selectrunningscripts.png

    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.
    Code:
    SELECT Name FROM runningscripts T1 INNER JOIN computers T2 ON T1.ClientID = T2.ClientID
    Code:
    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. #2
    SitePoint Member
    Join Date
    Mar 2014
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MySQL Multiple Inner Joins

    Hi there,

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

    Code:
    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. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •