CODEWITHSUNDEEP
mysqlsql
irobot96
irobot96

Reputation: 91

Joining two tables using sql with similar column names

I have two tables:

Table A.

| id       | name       | type     | value    |
| -------- | ---------- |----------|----------
| First    | row        | row      | row      |
| Second   | row        | row      | row      |

Table B.

| id       | name       |condition |
| -------- | ---------- |----------|
| First    | row        | row      | 
| Second   | row        | row      |

I want to select values from Table A based on the ids found in Table B. This I can do with a Join on TAbleA.id = tableB.id. But what If I want the name field from TableB and not TableA. How do I accomplish that?

The resulting tableC would be: Table C.

| id (from table A | name          | type          | value.        |
| found in B)      | (from tableB) | (from TableA) | (from TableA) |
| ---------------- | ------------- |---------------|---------------|
| First            | row           | row           | row           |
| Second           | row           | row           | row           |

Upvotes: 0

Views: 3483

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You list the columns you want in the select:

select a.id, b.name, a.type
from a join
     b
     on a.id = b.id;

If you want to select both, you can give them different aliases:

select a.id, a.name as a_name, b.name as b_name, a.type
from a join
     b
     on a.id = b.id

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

Qualify the columns as you already did in the ON clause by prefixing it with the table name.

SELECT tableb.name
       FROM tablea
            INNER JOIN tableb
                       ON tableb.id = tablea.id;

You can also alias the tables to have more meaningful or shorter identifiers.

SELECT b.name
       FROM tablea a
            INNER JOIN tableb b;
                       ON b.id = a.id;

If you join the same table more than once aliasing is even mandatory when qualifying any of their columns as the table name would not describe which instance is meant.

Upvotes: 0

Related Questions