# Miscellaneous > SQL Scripts >  join problem

## blee1

I have 2 tables
1st table= id,name         ex; 2,bob
2nd table= id,phonenumber  ex; 2,433-3333  -   2,555-4444
what I want is:
2,bob,433-3333,555-4444

I would like to have these joined,so the information is on the same row.
similar to this:
SELECT  distinct parents.name,parents_1.name, parents.phonenumber, parents_1.phonenumber
FROM parents INNER JOIN parents AS parents_1 ON parents.id = parents_1.id;

Right now this gives me two many rows.
  There are two rows in the second table that belong to one row in the first.  I want one row.

Any help is appreciated. Thanks

----------


## skhanal

You said there are duplicate rows in second table, but the query uses same table - parents.

Assuming second table name is parents_1

SELECT distinct parents.name,parents_1.name, parents.phonenumber, parents_1.phonenumber
FROM parents
INNER JOIN 
(select distinct name, phonenumber from parents_1) AS parents_1 
ON parents.id = parents_1.id;

----------


## blee1

Two tables

table1:
id   name
2     bob

table2
id   phonenumber
2     433-3333
2     555-4444

What I need is: 

id    name   phonenumber1  phonenumber2
2      bob    433-3333      555-4444

Thanks

----------


## Claire

select 
a.id,name,
case a.id when a.id then min(phone) end as Phone1,
case a.id when a.id then max(phone) end as Phone2
 from table1 a inner join table2 b on a.id = b.id
group by a.id,a.name

------Sample Data --------------

create table table1(id tinyint, name varchar(15))
insert into table1 select 2, 'bob'


create table table2(id tinyint, phone varchar(15))
id phonenumber
insert into table2 select 2, '433-3333'
insert into table2 select 2, '555-4444'

----------


## ddrozdov

You must have some other key to distinguish the phone numbers for the same person.
Say you have a column named ptype that can contain values like HOME, WORK then here's your query:

select 
 n.id, n.name,
 max(decode(p.ptype,'HOME',p.phonenum)) home,
 max(decode(p.ptype,'WORK',p.phonenum)) work
from
 names n, 
 phonenumbers p
where
 n.id = p.id
group by n.id, n.name

If you have to add more possible phone number types like CELL, then you have to add a column to your select list
max(decode(p.ptype,'CELL',p.phonenum)) mobile.

P.S. It makes no difference which group function you are using in place of max().  It could be count or min. sum cannot be used because it will raise ORA-01722 invalid number for ptype
d

----------

