Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    5

    Unanswered: Creating a result from three tables

    Hi all,

    I currently have three tables which store separate information;

    Code:
    hosts;
    -id
    -hostname
    -group
    
    group_members;
    -id
    -groupname
    -username
    
    users;
    -id
    -username
    -email
    I'm looking for a way to query all three tables and display the email address for all users which are a member of a certain group for a certain host.

    E.g.
    Code:
    select group from hosts where hostname = "examplehost"
    
    = examplegroup
    
    select username from group_members where groupname = "examplegroup"
    
    = user1
    
    select email from users where username = "user1"
    But all in one command. Is this possible? Would someone be kind enough to point me in the right direction? I assume I will need some sort of JOIN?

    Any help would be appreciated!

    Thanks

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Best guess at what the relationships are between the tables:

    Code:
    SELECT u.email
      FROM hosts h
      JOIN group_members g on g.groupname = 'examplegroup'
     INNER JOIN  users u on u.username = g.username
     WHERE h.hostname = 'examplehost'

  3. #3
    Join Date
    Dec 2012
    Posts
    5
    Quote Originally Posted by LinksUp View Post
    Best guess at what the relationships are between the tables:

    Code:
    SELECT u.email
      FROM hosts h
      JOIN group_members g on g.groupname = 'examplegroup'
     INNER JOIN  users u on u.username = g.username
     WHERE h.hostname = 'examplehost'
    Hi LinksUp, thanks for that.

    That's just what I need, apart from I dont want to have to define the groupname. E.g. the only variable I want to pass to the query is the hostname. Is that possible?

  4. #4
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by jayc89 View Post
    Hi LinksUp, thanks for that.

    . . . I don't want to have to define the groupname. E.g. the only variable I want to pass to the query is the hostname.
    Code:
    SELECT u.email
      FROM hosts h
     INNER JOIN group_members g on g.groupname = h.group
     INNER JOIN users u on u.username = g.username
     WHERE h.hostname = 'examplehost'
    Your example limited the output from one host and one group. This query will give you all users in all groups from one host.

Posting Permissions

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