Re: SQL Question

From: Steve Johnson <robertoc_at_fyiowa.infi.net>
Date: 1997/08/28
Message-ID: <34059A58.35D6@fyiowa.infi.net>#1/1

Try:
SELECT S.stock_no,

	S.dealer_no, 
	S.received_date

  FROM stock S,
(SELECT dealer_no
  FROM stock
GROUP BY dealer_no
HAVING COUNT(*) > 1) S1
WHERE s.dealer_no = s1.dealer_no

Steve J.

pchapp_at_sapient.com wrote:
>
> Hi SQL Gurus,
>
> I have problem in getting the following results. Any help will
> appreciated. I have a table like
>
> STOCK_TABLE
>
> stock_no dealer_no received_date
> __________ _________ ______________
>
> 1000 A111 12-JAN-97
> 2000 A111 12-JAN-97
> 3000 A222 13-JAN-97
> 4000 A333 14-JAN-97
> 5000 A333 14-JAN-97
>
> I want to find all the stock_no where I received that stock from the
> same dealer on the same day more than once. i.e. the result of the above
> sample data would be
>
> stock_no dealer_no received_date
> __________ _________ ______________
>
> 1000 A111 12-JAN-97
> 2000 A111 12-JAN-97
> 4000 A333 14-JAN-97
> 5000 A333 14-JAN-97
>
> By following sql I can get the dealer_no and received_date but not the
> stock_no.
>
> SELECT dealear_no, received_date
> FROM stock_table
> GROUP BY dealear_no, received_date
> HAVING count(*) > 1
>
> this will get
>
> dealer_no received_date
> _________ _____________
>
> A111 12-JAN-97
> A333 14-JAN-97
>
> Any help will be appreciated.
>
> Thank you,
>
> Prabha.
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
Received on Thu Aug 28 1997 - 00:00:00 CDT