Simple SQL Choose from 2 tables (what is Join?)

Question

I am new to SQL. I have a simple problem getting results from two different tables.

I have two tables in the database. The first table has a column with an id link that matches the rows in the second table. What SELECT do I need to execute in order to get the result, so that the identifiers are recounted by all the values ​​in the second table. To visualize the tables I'm 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!

Answer 1

You really want to join 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 should match in the two tables, and use on to tell SQL to match them. This table has an identifier column that allows you to do this: you join the table, on , and then list the values ​​that should be equal.

If you do not need all the columns in both tables, you can simply list only those columns that you need in the last query. This means that instead of Select * you specify the columns you want. As shown below, if a column is displayed with the same name in both tables, you need to add the table name so that SQL knows what value you want.

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

Answer 2

Do you want to UNITE:

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