Home » SQL & PL/SQL » SQL & PL/SQL » Query
Query [message #268357] Tue, 18 September 2007 05:50 Go to next message
webadministrator
Messages: 45
Registered: October 2005
Member
Hi,

i have 2 tables Customer and Phone. for every customer i have several phones. i want to do a query that will retrieve only 1 phone, regardless which one.i did the following query:

select customer.code_cust, phone.phone_num
from customer, phone
where customer.code_cust = phone.code_cust

but im getting duplicate answers i.e for each customer im getting all the phones.

thank you.
Re: Query [message #268358 is a reply to message #268357] Tue, 18 September 2007 05:58 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

append, with where clause

and rownum=1;
Re: Query [message #268373 is a reply to message #268357] Tue, 18 September 2007 06:18 Go to previous messageGo to next message
webadministrator
Messages: 45
Registered: October 2005
Member
i didn't get what you mean..
Re: Query [message #268375 is a reply to message #268357] Tue, 18 September 2007 06:22 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

select customer.code_cust, phone.phone_num
from customer, phone
where customer.code_cust = phone.code_cust and rownum=1;
Re: Query [message #268376 is a reply to message #268373] Tue, 18 September 2007 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't you understand? "append"? "where clause"?
where ... and rownum=1

Regards
Micehl
Re: Query [message #268378 is a reply to message #268357] Tue, 18 September 2007 06:28 Go to previous messageGo to next message
webadministrator
Messages: 45
Registered: October 2005
Member
But the rownum = 1 will only get me as a result the first record from the whole table.
what i need is all the customers but if several phone numbers exist i only want one of them to be displayed in the result.
Re: Query [message #268379 is a reply to message #268378] Tue, 18 September 2007 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, you have to be clearer. Remember your spec: "i want to do a query that will retrieve only 1 phone".
SQL> select deptno, ename 
  2  from (select deptno, ename, 
  3               row_number () over (partition by deptno order by null) rn 
  4        from emp)
  5  where rn = 1
  6  order by deptno
  7  /
    DEPTNO ENAME
---------- ----------
        10 CLARK
        20 SMITH
        30 ALLEN

3 rows selected.

Regards
Michel
Re: Query [message #268394 is a reply to message #268378] Tue, 18 September 2007 07:37 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
webadministrator wrote on Tue, 18 September 2007 07:28
But the rownum = 1 will only get me as a result the first record from the whole table.
what i need is all the customers but if several phone numbers exist i only want one of them to be displayed in the result.



Then use MIN or MAX.
Re: Query [message #268397 is a reply to message #268357] Tue, 18 September 2007 07:44 Go to previous message
webadministrator
Messages: 45
Registered: October 2005
Member
Thats what i actually did.
Thank you for all the replies.
Previous Topic: Invalid file operation problem with 10g
Next Topic: Format date?
Goto Forum:
  


Current Time: Tue Dec 06 16:14:43 CST 2016

Total time taken to generate the page: 0.10653 seconds