Home » SQL & PL/SQL » SQL & PL/SQL » sql query
sql query [message #196823] Sun, 08 October 2006 00:36 Go to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member

hello

i want to retrive the order_no, client_no, salesman_no where a client has been serviced by more than one one salesman from the sales_order table?

ORDER_ ORDER_DAT CLIENT SALESM D B DELY_DATE ORDER_STA
------ --------- ------ ------ - - --------- ---------
O19001 12-JAN-96 C00001 S00001 f n 20-JAN-96 in proces
O19002 25-JAN-96 C00002 S00002 p n 27-JAN-96 cancelled
O46865 18-FEB-96 C00003 S00003 f y 20-FEB-96 fulfilled
O19003 03-APR-96 C00001 S00001 f n 07-APR-96 fulfilled
O46866 20-MAY-96 C00004 S00002 p n 22-MAY-96 cancelled
O19008 24-MAY-96 C00005 S00004 f n 26-MAY-96 in process

please c where i had done wrong in query.

SELECT FIRST.ORDER_NO, FIRST.CLIENT_NO, FIRST.SALESMAN_NO, SECOND.SALESMAN_NO FROM SALES_ORDER FIRST, SALES_ORDER SECOND WHERE FIRST.SALESMAN_NO=SECOND.SALESMAN_NO AND FIRST.SALESMAN_NO<>SECOND.SALESMAN_NO;

No rows selected

why no rows selected?



Re: sql query [message #196847 is a reply to message #196823] Sun, 08 October 2006 09:16 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This could be one way to do it:
SELECT order_no, client_no, salesman_no
  FROM SALES_ORDER
 WHERE client_no IN (SELECT   client_no
                         FROM SALES_ORDER s
                     GROUP BY client_no
                       HAVING COUNT (DISTINCT salesman_no) > 1)
Re: sql query [message #196907 is a reply to message #196823] Mon, 09 October 2006 02:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I have to ask this. Did you even look at the query you'd written before posting this?
You've got two clauses in the query:
WHERE FIRST.SALESMAN_NO=SECOND.SALESMAN_NO
and
AND FIRST.SALESMAN_NO<>SECOND.SALESMAN_NO
which are completely exclusive.

Re: sql query [message #197735 is a reply to message #196907] Thu, 12 October 2006 08:23 Go to previous messageGo to next message
ashish_pass1
Messages: 114
Registered: August 2006
Location: delhi
Senior Member
yes, i had seen it before posting.
if i use second clause i dnt get any rows selected.

[Updated on: Thu, 12 October 2006 08:24]

Report message to a moderator

Re: sql query [message #197742 is a reply to message #197735] Thu, 12 October 2006 08:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes. You don't get any rows returned because there are no (and I mean none, at all, anywhere in any part of the universe) values that can satisfy both
WHERE FIRST.SALESMAN_NO=SECOND.SALESMAN_NO
and
AND FIRST.SALESMAN_NO<>SECOND.SALESMAN_NO


Try replacing
WHERE FIRST.SALESMAN_NO=SECOND.SALESMAN_NO
with
WHERE FIRST.ORDER_NO=SECOND.ORDER_NO
AND   FIRST.CLIENT_NO=SECOND.CLIENT_NO
I haven't tested it, so I can't guarantee it will work, but I can guarantee it won't be any worse.
Re: sql query [message #197784 is a reply to message #197742] Thu, 12 October 2006 12:45 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
JRowbottom wrote on Thu, 12 October 2006 09:44

Yes. You don't get any rows returned because there are no (and I mean none, at all, anywhere in any part of the universe) values that can satisfy both
WHERE FIRST.SALESMAN_NO=SECOND.SALESMAN_NO
and
AND FIRST.SALESMAN_NO<>SECOND.SALESMAN_NO




You mentioned "universe" which would include Bizzaro World, in which case that statement would indeed work. You probably just meant to say "world."
Previous Topic: Calling Win32 files ( EXE or DLL) from PL/SQL
Next Topic: Help needed
Goto Forum:
  


Current Time: Wed Dec 07 14:43:18 CST 2016

Total time taken to generate the page: 0.12143 seconds