Beginner query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Beginner query

Author

Topic

Teufel9
Starting Member

1 Post

Posted - 2002-10-18 : 08:06:15

I am a beginner at SQL, and there are some things I am just having a lot of trouble getting my head around, so if anyone can help I would greatly appreciate it! My problem:

I have 2 tables:

1. Sales staff
Name
-----------------------------------------
STAFF_NO
INITIALS NAME
TITLE

2. Product - Sales link

Name
----------------------------------------- PROD_CODE STAFF_NO
PROD_NO

Table 2 is linked to table 1 by the staff code. It shows what products a Sales rep sells (a sales rep may sell many products).

I need to write a query that outputs a list of unique pairs of sales reps who sell the same products. I have no idea where to begin, I tried a few things but it has become increasingly clear I have no idea, my latest try is:

select first.name as "Name", second.name as "Name", u.room_no as "Room"
from l first, l second,u
where u.room_no in (select
first.room_no, second.room_no
from u first, u second
where first.room_no =second.room_no);

Currently I am getting an error at line 3 (ORA-00913: too many values), but I fear it is just one of many errors!

Anyone who could help me, I would be eternally grateful.

mr_mist
Grunnio

1870 Posts

Posted - 2002-10-18 : 08:30:27

I'm slightly confused because your tables are about sales people and products and your query is about room numbers and names, but

select Title + Initials + Name as [Sales Person], people.[sales person], prod_no
inner join products on
ss.staff_no = products.staff_no
inner join
(Select Title + Initials + Name as [Sales Person], prod_no from [Sales Staff] ss
inner join products p on
ss.staff_no = p.staff_no) people
on people.prod_no = products.prod_no and
people.staff_no <> ss.staff_no

Something like that..

Edited by - mr_mist on 10/18/2002 08:31:33

scottpt
Posting Yak Master

186 Posts

Posted - 2002-10-18 : 11:24:19

Are you suggesting that Oracle can handle ANSI92 syntax? Surely you jest.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-18 : 11:32:16

quote:

Are you suggesting that Oracle can handle ANSI92 syntax? Surely you jest.

No, it's in Oracle 9i.
http://www.iss.net/security_center/static/8855.php
Now that's funny.

Edited by - Arnold Fribble on 10/18/2002 11:38:46

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-18 : 11:52:51

You can also try:

SELECT C.StaffName, D.StaffName, A.Prod_No
FROM Products As A
INNER JOIN Products As B ON A.Staff_No <> B.Staff_No AND A.Prod_No = B.Prod_No
LEFT JOIN Staff C ON A.Staff_No = C.Staff_No
LEFT JOIN Staff D ON B.Staff_No = D.Staff_No

This is a SQL SERVER SOLUTION

Edited by - ValterBorges on 10/18/2002 11:54:53

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-18 : 11:59:47

quote:
http://www.iss.net/security_center/static/8855.php
Now that's funny.

ROFLMMFAO!

Actually Arnold, that is HILARIOUS! I wonder if this means that Oracle is still unbreakable, since you don't actually have to break in for that one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources