CODEWITHSUNDEEP
mysqlsql
Benedict Lewis
Benedict Lewis

Reputation: 2813

Union select using value returned from previous select

I have a database set up like this with two tables:

CREATE TABLE users 
    (
     id int auto_increment primary key, 
     username varchar(20), 
     password varchar(30),
     auth_ip varchar(15),
     account_type varchar(10),
     server_name varchar(20)
    );

CREATE TABLE servers 
    (
     id int auto_increment primary key, 
     name varchar(20), 
     address varchar(30),
     reserved boolean,
     user varchar(20)
    );

I am trying to write a query which allows me to search for users by username, and then include the information for from the servers table for the row where server_name is name. So for example, I might have 200 users, and 5 servers, so when I search for username foo521 it returns the information from the user table, as well as from the server table for the server defined by server_name.

I'm new to SQL, so from basic googlefu I thought the best option would be to do something like:

SELECT * FROM users WHERE username = "foo"
UNION
SELECT address FROM servers WHERE name = something.server_name

The important thing is that this returns the results with one query, rather than multiples. Can anyone give some input? I'm using SQLite so AFAIK it must be a flat file database but I could be wrong about that.

Upvotes: 1

Views: 1794

Answers (2)

Tiny Haitian
Tiny Haitian

Reputation: 479

You would want to do something of this nature:

SELECT u.*
FROM `users` AS u
INNER JOIN `servers` AS s
    ON u.server_name = s.name
WHERE u.username = 'foo'

I would look up how to join tables like here Things to point out that would see in both @Lamak's post and mine:

  • Inner joins will only display data that has some tie in both tables; if it doesn't exist in one or the other, the record won't show up.
  • Left/Right joins will give you null results if one of the referenced tables doesn't have correlating information.
  • Always use aliases, and specify them in your select statement. If you don't, it will return return records on all joined tables. This is fine if you're expecting that to happen.

  • As pointed out by @Winkbrace, make sure you set your primary / foreign keys appropriately. One of your Primary id's should be a foreign key in the other table. Once you do, you should have something like the following on the join:

      INNER JOIN `servers` AS s ON u.server_id = s.server_id

Upvotes: 2

Lamak
Lamak

Reputation: 70638

SELECT *
FROM `users` AS u
LEFT JOIN `servers` AS s
    ON u.server_name = s.name
WHERE u.username = 'foo'

Upvotes: 2

Related Questions