Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

sql join 2 rows in the same table

Tags:

sql

mysql

oracle

I have the following table:

Name   Type     Value
---------------------
mike   phone    123    
mike   address  nyc    
bob    address  nj    
bob    phone    333

I want to have the result like this:

name  value  value
-------------------
mike  nyc    123
bob   nj     333

How can I do it?

like image 569
Dejell Avatar asked Jul 07 '11 17:07

Dejell


1 Answers

it is called a self-join. the trick is to use aliases.

select 
    address.name,
    address.value as address,
    phone.value as phone
from
    yourtable as address left join
    yourtable as phone on address.name = phone.name
where address.type = 'address' and
      (phone.type is null or phone.type = 'phone')

The query assumes that each name has an address, but phone numbers are optional.

like image 140
cdonner Avatar answered Oct 04 '22 00:10

cdonner