CODEWITHSUNDEEP
sqljoinmultiple-tables
skibulk
skibulk

Reputation: 3188

Simple SQL Select from 2 Tables (What is a Join?)

I'm new to SQL. I have a simple problem with getting the results from two different tables.

I have two tables in a database. The first table has a column with an id reference, which corresponds to rows in the second table. What SELECT do I need to perform to get a result such that the ids are repalced by all of the values in the second table. To visualize the tables I am discussing:

TABLE_USERS
===========
id      username      group
--      --------      -----
1       jim           A
2       alice         A
3       brandon       B

TABLE_GROUPS
============
id      groupname         members
--      ---------         -------
A       designer          134
B       photographer      39

DESIRED_SELECTION
=================
id      username      group
--      --------      -----
1       jim           designer
2       alice         designer
3       brandon       photographer

Thanks!

Upvotes: 0

Views: 1398

Answers (2)

David Manheim
David Manheim

Reputation: 2626

You do, in fact, want to JOIN the two tables: 

SELECT * FROM
    TABLE_USERS LEFT JOIN TABLE_GROUPS 
    ON TABLE_USERS.group = TABLE_GROUPS.id

The trick of joining tables is to find the values that must match in the two tables, and use the on to tell SQL to match them. This table has a ID column to let you do that = you will join the table, ON, and then list the values that need to be equal.

If you do not want all of the columns in both tables, you can simply list only the columns you need in your final query. This means that instead of Select *, you list the columns you want. As shown below, if a column appears with the same name in both tables, you need to prepend the table name, so that SQL know which value you want.

SELECT TABLE_USERS.ID, Username, Groupname 
  FROM TABLE_USERS 
     LEFT JOIN TABLE_GROUPS 
     ON TABLE_USERS.group = TABLE_GROUPS.id

Upvotes: 2

alexn
alexn

Reputation: 58992

You want a JOIN:

SELECT
    u.id,
    username,
    groupname
FROM
    TABLE_USERS AS u
LEFT JOIN TABLE_GROUPS AS g
    ON u.group = g.id

Upvotes: 2

Related Questions