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

Question

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!

@John Conde - well I really don't know where to start. I've been reading about Table Joins but they don't seem to substitute data, rather multiple columns are brought together without dropping one — skibulk, Jun 14 '12 at 20:08
You don't need to have TABLE_GROUPS.members column - that can be derived in SQL by doing a `COUNT()` on TABLE_USERS. That said, some systems do this sort of duplication (store a count manually) for optimisation reasons - useful if you have very complex queries or huge tables. — halfer, Jun 14 '12 at 20:11
@jcho360 - alpha foreign keys aren't so bad. Keys don't have to be auto increment or use a `SEQUENCE` either. — halfer, Jun 14 '12 at 20:12
possible duplicate of [Get data from multiple tables](http://stackoverflow.com/questions/8726055/get-data-from-multiple-tables) — Stephen C, Jun 16 '12 at 07:50

score 2 · Answer 1 · answered Jun 14 '12 at 20:07

2

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

answered Jun 14 '12 at 20:07

alexn

57,867
14
111
145

+1. Although that `LEFT` could be an `INNER`, unless it is possible for a user not to have a group. – halfer Jun 14 '12 at 20:09
@halfer Absolutely - but we don't know. – alexn Jun 14 '12 at 20:10

score 2 · Accepted Answer · answered Jun 14 '12 at 20:20

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

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

2 Answers2

Linked

Related