CODEWITHSUNDEEP
mysqljoin
sleepless_in_seattle
sleepless_in_seattle

Reputation: 2234

MySQL join two tables into one

I have two imaginary tables:

1. People

id, age
1, 25

2. Names

id, people_id, type, value
1, 1, 'first', 'John'
2, 1, 'last', 'Doe'

How can I join the two tables so I'd get this result:

id, age, first, last
1, 25, 'John', 'Doe'

Upvotes: 0

Views: 678

Answers (4)

Krish R
Krish R

Reputation: 22741

Hope it helps you,

Method: 1

 select p.id, p.age, n.type as first, n.value as last from People as p, Names as n where p.id = n.people_id

Method: 2

 select p.id, p.age, n.type as first, n.value as last from People as p LEFT JOIN Names as n ON p.id = n.people_id

Upvotes: 0

Nik Terentyev
Nik Terentyev

Reputation: 2310

SELECT p.id, p.age, n.first, n.last
FROM People p
JOIN Names n 
ON p.id = n.people_id;

Upvotes: 0

FeliceM
FeliceM

Reputation: 4219

You are looking for general information on how to join data from different tables. It is called inner join. Reference can be found here.

Upvotes: 0

John Woo
John Woo

Reputation: 263903

SELECT  a.ID,
        a.Age,
        MAX(CASE WHEN b.type = 'first' THEN b.Value END) `First`,
        MAX(CASE WHEN b.type = 'last' THEN b.Value END) `Last`
FROM    People a
        LEFT JOIN Names b
            ON a.ID = b.people_id
GROUP   BY a.ID, a.Age

Otherwise, if you have unknown values on column type, a dynamic statement is much more preferred.

SET @sql = NULL;

SELECT  GROUP_CONCAT(DISTINCT
        CONCAT('MAX(CASE WHEN b.type = ''',
               type,
               ''' THEN value ELSE NULL END) AS ',
               CONCAT('`', type, '`')
               )) INTO @sql
FROM Names;

SET @sql = CONCAT('SELECT  a.ID,
                            a.Age, ', @sql , '
                    FROM    People a
                            LEFT JOIN Names b
                                ON a.ID = b.people_id
                    GROUP   BY a.ID, a.Age');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Upvotes: 3

Related Questions