HELP with Select statement

[Leon1977]

I have something like this

name phones
--------- ---------
Bob 5552626
Bob 6668654
Bob 7586666

Select name, phone
from names n, phones p
where n.name_id=p.fk_name_id
and Upper(name)=Upper('Bob')

Is there a way with a single query I can get this result

name phones
--------- ---------------------------
Bob 5552626, 6668654, 7586666

 

[sem]

I suppose that you may use pl/sql:

function phoneList(pName in varchar2) is
mList varchar2(100);
begin
for f in (select phone where upper(name) = upper(pName))
loop
if mList is null then
mList := f.phone;
else
mList := mList || f.phone;
end if;
end loop;
return mList;
end;

And than use

Select 'Bob' name, phoneList('Bob') phones from dual.

 

[Leon1977]

thanx a lot... it will do , but I am just curious is there a way i can do this with a single Select statement ???

 

[sem]

Unfortunately, I'm about to be sure that the answer is NO (the result you need contradicts to the 1st normal form). Of course If you know the maximum number of phone numbers for one person beforehand, you may play with self joins, but this approach is quite unefficient to be used in real life.

 

[srishan]

As sem mentioned already, it is ONLY possible to write a SQL if we know the number of phones upfront, sample code here with.

SQL> select * from names;

NAME            PHONE
---------- ----------
Bob           5552626
Bob           6668654
Bob           7586666

SQL> select name, 
2 max(decode(rownum, 1, phone, null)) phone_1,               
3 max(decode(rownum, 2, phone, null)) phone_2,
4 max(decode(rownum, 3, phone, null)) phone_3
5 from   names
6 group by name;

NAME          PHONE_1    PHONE_2    PHONE_3
---------- ---------- ---------- ----------
Bob           5552626    6668654    7586666

Thx,
SriDHAR

 

[cakani]

You need to use table aliases
You need to make query where you repeat 3 times tables with phones and use 3 joints
Select name, a.phone1, b.phone2,c.phone1
from names n, phones a ,phones b, phones c
where n.name_id=a.fk_name_id
and n.name_id=b.fk_name_id
and n.name_id=c.fk_name_id
and Upper(name)=Upper('Bob')

 

[Leon1977]

Thanx for the help