-
join problem
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
Last edited by blee1; 03-11-2004 at 10:09 AM.
-
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;
-
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
-
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'
-
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
Last edited by ddrozdov; 04-12-2004 at 02:38 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|