SQL: Having a table twice in the FROM clause

Question

I am using MySQL. Here is my schema:

Suppliers(sid: integer, sname: string, address string)

Parts(pid: integer, pname: string, color: string)

Catalog(sid: integer, pid: integer, cost: real)

(primary keys are bolded)

I am trying to write a query that selects pairs of sids that supply the same part:

-- Find pairs of SIDs that both supply the same part
SELECT s1.sid, s2.sid
FROM Suppliers AS s1, Suppliers AS s2
JOIN Catalog ON s1.sid = Catalog.sid OR s2.sid = Catalog.sid;

MySQL gives me this error:

ERROR 1054 (42S22): Unknown column 's1.sid' in 'on clause'

What am I doing wrong?

Answer 1

You're mixing ANSI-89 and ANSI-92 JOIN syntax - you can only use one, or the other. ANSI-92:

   SELECT s1.sid, s2.sid
     FROM CATALOG c
LEFT JOIN SUPPLIERS s1 ON s1.sid = c.sid
LEFT JOIN SUPPLIERS s2 ON s2.sid = c.sid

Omit the LEFT keyword if you want to see categories with two suppliers associated.

ANSI-89 syntax has all the tables involved declared in the FROM clause, joins are in the WHERE clause.

Use ANSI-92 - see this question for details.

Answer 2

You are joining s2 and Catalog. s1 doesn't exist in that clause.

Answer 3

I think you need to explicity join all the tables if you will be using explicit joins.

e.g.

-- Find pairs of SIDs that both supply the same part
SELECT 
  s1.sid, 
  s2.sid
FROM 
  Catalog 

    LEFT OUTER JOIN 
  Suppliers AS s1,
    ON Catalog.sid = s1.sid

    LEFT OUTER JOIN
  Suppliers AS s2
    ON Catalog.sid = s2.sid 

Answer 4

I don't understand the error message, but:

I would avoid using join in this case. Try this

SELECT s1.sid, s2.sid
FROM suppliers s1,
     suppliers s2,
     catalog   c1,
     catalog   c2
WHERE c1.pid = c2.pid
AND   s1.sid = c1.sid
AND   s2.sid = c2.sid
AND   s1.sid < s2.sid

Although since all you're asking for is the sids, it can be simpler:

SELECT c1.sid, c2.sid
FROM catalog   c1,
     catalog   c2
WHERE c1.pid = c2.pid
AND   c1.sid < c2.sid

Answer 5

You need to join catalog with itself

SELECT 
    pid, 
    c1.sid, 
    c2.sid
FROM Catalog c1
JOIN Catalog c2 ON c1.pid = c2.pid AND c1.sid < c2.sid

< condition is to avoid pairs (A supplies same X as B so B supplies same X as A)

Answer 6

find parts with two or more suppliers:

select part_id
from catalog 
group by part_id
having count(part_id) >= 2

find the supplier(s) of those parts, more future-proof, can show two or more suppliers:

select c.part_id, s.supplier_name 
from catalog c
join supplier s
where c.part_id in (
    select part_id
    from catalog 
    group by part_id
    having count(part_id) >= 2)
order by c.part_id, s.supplier_name

but if you want parts which exactly has two suppliers only:

select c.part_id, group_concat(s.supplier_name) as suppliers 
from catalog c
join supplier s using(supplier_id)
where part_id in (
    select part_id
    from catalog 
    group by part_id
    having count(part_id) = 2)
group by c.part_id

if you want only those two suppliers to display in two columns.. me thinking too... :-)

[UPDATE]

what i thought up:

select c.part_id, c.min(c.supplier_id) as first, c.max(c.supplier_id) as second 
from catalog c
join supplier s
where c.part_id in (
    select part_id
    from catalog 
    group by part_id
    having count(part_id) = 2)
group by c.part_id
order by c.part_id

to get the supplier names:

select x.part_id, a.supplier_name, b.supplier_name from
(
    select c.part_id, c.min(c.supplier_id) as first, c.max(c.supplier_id) as second 
    from catalog c
    join supplier s
    where c.part_id in (
        select part_id
        from catalog 
        group by part_id
        having count(part_id) = 2)
    group by c.part_id
    order by c.part_id
 ) as x
 join supplier a on x.first = a.sid
 join supplier b on x.second = b.sid