Take the 2-minute tour ×

Below is how I have table.

create table tab1 ( id int, myname varchar(20));

insert into tab1 values 
(1, 'name 1'),
(2, 'name 2');

create table tab2 ( id int, tab2id int, type varchar(20), value varchar(20));
insert into tab2 values
(1,2,'location','location 1'),
(2,4,'location','location 2');

What I want is as below.

myname   |   phone    | location  
 name 1  |  12345671  | location 1
 name 2  |  12345672  | location 2

Any idea how to get this done?

dummy data to check query

share|improve this question
what you have tried? –  diEcho Oct 10 '12 at 8:19
@diEcho : I believe I will need to use CASE statement... right? –  Fahim Parkar Oct 10 '12 at 8:22
I think you should change table structure. –  diEcho Oct 10 '12 at 8:23

5 Answers 5

up vote 0 down vote accepted
select t1.myname,t2.value as phone,t3.value as location from tab1 t1 
inner join tab2 t2 on t1.id=t2.id and t2.type='phone' 
inner join tab2 t3 on t1.id=t3.id and t3.type='location' 

SQL FIDDLE here http://sqlfiddle.com/#!2/16112/6

share|improve this answer
select x.myname as name, a.value as Phone, b.value as Location
from tab1 x,
(select value, id
from tab2 
where type='phone' 
group by id)a,
(select value, id
from tab2 
where type='location' 
group by id)b
where x.id = a.id
and x.id = b.id;


share|improve this answer

Never mind, I'm way off here....

Try this one:

select tab1.id, tab1.myname, tab2.type, tab2.value from tab1 join tab2 on tab1.id=tab2.id 
JOIN tab2 as t2 ON tab1.id=tab2.id WHERE t2.tab2id=2;


share|improve this answer
incorret output... check ur link again –  Fahim Parkar Oct 10 '12 at 8:25

It is better to change table structure as diEcho suggested, but for your case you can use next query (pivot table). Run this query, and try to JOIN it with first table -

  MAX(IF(type = 'phone', value, NULL)) phone,
  MAX(IF(type = 'location', value, NULL)) location
share|improve this answer

try this:

select * from 
(select (select myname from tab1 where id=t1.id) myname,CASE WHEN t1.type='phone' then t1.value end as 'Phone', (select value from tab2 where id=t1.id and type='location') 'Location'
from tab2 t1) a where phone is not null 

SQL Fiddle Demo

share|improve this answer

Your Answer


