#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2016
    Posts
    26
    Rep Power
    0

    Query table with two foreign keys linked to 2 additional tables


    Hello,

    I have 3 tables: servers, groups & osversion. There are 2 foreign keys in the servers table that links to an entry in the groups & osversion tables.

    The table structure is as follows:

    Servers
    server_id(PK)
    server_name
    server_hostname
    server_ip
    server_osversion(FK)
    server_password
    group_id(FK)

    Groups
    group_id(PK)
    group_name

    OSVersion
    os_id(PK)
    osversion

    I am having trouble creating a query that will display the servers with their respective group name and OS version. I am fairly new to SQL and
    know that it will require JOINs but after searching and trying different things I can't get it right and hoping someone can help me. I'm sure this
    one should be pretty easy for all the SQL gurus out there!

    Thanks!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,698
    Rep Power
    4288
    Code:
    SELECT servers.server_id
           servers.server_name         
           servers.server_hostname     
           servers.server_ip     
           osversion.osversion      
           servers.server_password     
           groups.group_name
      FROM servers
    INNER
      JOIN osversion
        ON osversion.os_id = servers.server_osversion
    INNER
      JOIN groups
        ON groups.group_id = servers.group_id
    tip: do not embed the name of the table into the name of the column, as this increases query text noise over signal

    Comments on this post

    • xerces1024 agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo