CODEWITHSUNDEEP
sqlpostgresql
KellyJ
KellyJ

Reputation: 1

select a pair from an SQL table

I want to select a pair of supplier ID that meets a certain requirement, i.e. the first supplier charges more than the second one for a certain part. 

SELECT DISTINCT S1.sid, S2.sid
FROM suppliers JOIN catalog ON (suppliers.sid = catalog.sid) AS S1,
suppliers JOIN catalog ON (suppliers.sid = catalog.sid) AS S2
WHERE S1.pid = S2.pid AND S1.cost > S2.cost;

Now this isn't working. Can anyone tell me where the problem is?

Upvotes: 0

Views: 129

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

One problem with your query is that you have the same table multiple times in the from clause without table aliases to distinguish among the different references.

Here is a different approach that uses a CTE to combine suppliers and catalogs and then a simple join:

with sc as (
      select s1.sid, pid, cost
      from suppliers s1 join
           catalog c1
           on s1.sid = c1.sid
     )
select distinct sc1.sid, sc2.sid
from sc sc1 join
     sc sc2
     on sc1.pid = sc2.pid and sc1.cost > sc2.cost;

This request seems like an odd request -- if you have multiple suppliers for a given part, you will get many rows.

EDIT:

It strikes me that you don't even need the suppliers table at all:

select distinct c1.sid, c2.sid
from catalog c1 join
     catalog c2
     on c1.pid = c2.pid and c1.cost > c2.cost;

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726809

You made a mistake in the way you expresses the joins: the table on the left (i.e. suppliers) does not need to be repeated for each table being added to the query.

Here is how you can fix the query using table aliases:

SELECT DISTINCT S1.sid, S2.sid 
FROM suppliers s
JOIN catalog s1 ON s.sid=s1.sid
JOIN catalog s2 ON s.sid=s2.sid 
WHERE S1.pid=S2.pid AND S1.cost>S2.cost

Upvotes: 1

Related Questions