Home » SQL & PL/SQL » SQL & PL/SQL » All records
All records [message #193484] Sun, 17 September 2006 16:20 Go to next message
Izaac
Messages: 5
Registered: September 2006
Junior Member
Hi,
I need to find out which salesman has sold all the products.
Tables descriptions are as follows:
Salesman:

SID SNAME AGE RATING
--------------- --------------- ---------- ---------------
101 Omer 21 A
102 hashim 22 A
103 Dar 22 A
104 Fahad 22 A
105 Abid 50 C

Product:

PNAME PID COLOR
--------------- --------------- ---------------
Turbo b01 red
Turbo2 b02 red
Turbo3 b03 green
Turbo4 b04 blue
Turbo5 b05 red

Transaction:

SID PID DAT
--------------- --------------- ---------------
101 b01 16 sep 06
101 b02 16 sep 06
102 b01 17 sep 06
102 b02 18 sep 06
103 b03 19 sep 06
105 b04 22 sep 06
101 b03 18 sep 06
101 b04 20 sep 06
101 b05 1 sep 06

please advise.
Re: All records [message #193485 is a reply to message #193484] Sun, 17 September 2006 17:06 Go to previous message
gojko
Messages: 18
Registered: September 2006
Location: London
Junior Member
create a list of salesmen with number of distinct sold products equal to total number of products

SELECT SID, NAME FROM salesman WHERE SID IN (
SELECT SID FROM TRANSACTION GROUP BY SID HAVING COUNT(DISTINCT pid)=(SELECT COUNT(*) FROM product)
)

Gojko Adzic
http://www.gojko.com
Previous Topic: Finding atleast two records
Next Topic: Group statement
Goto Forum:
  


Current Time: Sat Dec 10 06:47:13 CST 2016

Total time taken to generate the page: 0.13314 seconds