CODEWITHSUNDEEP
mysqljoin
Fahim Parkar
Fahim Parkar

Reputation: 31637

print data of two table

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,1,'phone','12345671'),
(1,2,'location','location 1'),
(2,3,'phone','12345672'),
(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

Upvotes: 0

Views: 101

Answers (5)

AnandPhadke
AnandPhadke

Reputation: 13496

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

Upvotes: 0

jt234
jt234

Reputation: 672

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;

SQL FIDDLE Here

Upvotes: 1

Devart
Devart

Reputation: 121922

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 -

SELECT
  id,
  MAX(IF(type = 'phone', value, NULL)) phone,
  MAX(IF(type = 'location', value, NULL)) location
FROM
  tab2
GROUP BY
  id

Upvotes: 0

Nin
Nin

Reputation: 3020

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;

http://sqlfiddle.com/#!2/80d02/7

Upvotes: 0

sel
sel

Reputation: 4957

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

Upvotes: 0

Related Questions