Home » SQL & PL/SQL » SQL & PL/SQL » Finding atleast two records
icon4.gif  Finding atleast two records [message #193476] Sun, 17 September 2006 14:03 Go to next message
Izaac
Messages: 5
Registered: September 2006
Junior Member
Hi,
I have to retrieve records,in sql, of a person with at least two sales. I have searched on the Internet and tried all the joins that i knew off, but still can't get the records. I know it has to be done through the Cartesian product of the two tables but i am unable to find the solution. Please advise!
Re: Finding atleast two records [message #193477 is a reply to message #193476] Sun, 17 September 2006 14:31 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Show us the table DESCRIPTIONS & show us what you have tried already by cut & paste SQL & results

[Updated on: Sun, 17 September 2006 14:32] by Moderator

Report message to a moderator

Re: Finding atleast two records [message #193479 is a reply to message #193477] Sun, 17 September 2006 14:53 Go to previous messageGo to next message
Izaac
Messages: 5
Registered: September 2006
Junior Member
Hey,

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


My tires are as follows:

SQL>select sname from salesman,transaction where salesman.sid = transaction.sid AND salesman.sid != transaction.sid;
SQL>select sname from salesman,transaction where salesman.sid = transaction.sid AND product.pid != transaction.pid;
Re: Finding atleast two records [message #193480 is a reply to message #193476] Sun, 17 September 2006 15:20 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
WHERE 2 >= ..... you fill in the dots
Re: Finding atleast two records [message #193481 is a reply to message #193480] Sun, 17 September 2006 15:41 Go to previous messageGo to next message
Izaac
Messages: 5
Registered: September 2006
Junior Member
This query is working:
SQL> Wrote file afiedt.buf

1 select distinct p.sname
2 from salesman s, salesman p, transaction r1, transaction r2
3 WHERE p.sname=s.sname and s.sid=r1.sid and s.sid=r2.sid
4* and r2.pid<>r1.pid
SQL> /

SNAME
---------------
Omer
hashim
Re: Finding atleast two records [message #193482 is a reply to message #193476] Sun, 17 September 2006 16:03 Go to previous messageGo to next message
gojko
Messages: 18
Registered: September 2006
Location: London
Junior Member
use group by with a having clause:

select sid, sname, count(*) as numtransactions from salesman,transaction where salesman.sid = transaction.sid
group by sid, sname
having count(*)>2

Gojko Adzic
http://www.gojko.com
Re: Finding atleast two records [message #193483 is a reply to message #193482] Sun, 17 September 2006 16:10 Go to previous message
Izaac
Messages: 5
Registered: September 2006
Junior Member
yeah, that can also be done.
thankx
Previous Topic: Query on Outer Join
Next Topic: All records
Goto Forum:
  


Current Time: Wed Dec 07 22:26:30 CST 2016

Total time taken to generate the page: 0.06777 seconds