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?

score 1 · Answer 1 · edited May 23 '17 at 12:26

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.

edited May 23 '17 at 12:26

Community

1
1

answered Feb 23 '10 at 22:55

OMG Ponies

325,700
82
523
502

@wwosik: Me too, but with the `OR` it's more likely they could be optional. – OMG Ponies Feb 23 '10 at 23:00
If I understand it well, OP wants pairs of suppliers that both supply a thingie X. He doesn't want products which are supplied only by one or none suppliers. That's why I deleted my comment... – user76035 Feb 23 '10 at 23:08
@wwosik: I added more info, but the orig. query is joining to the same column - the values will be duplicates. – OMG Ponies Feb 23 '10 at 23:19
At the moment I think you got it sort of wrong. It will match every product from Catalog twice with the same supplier who delivers it. ;) – user76035 Feb 23 '10 at 23:45

score 1 · Answer 2 · answered Feb 23 '10 at 22:56

1

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

answered Feb 23 '10 at 22:56

brian

1,080
9
7

If that were the case, you wouldn't be able to join to the `s2` alias either. – OMG Ponies Feb 23 '10 at 23:12
Both are declared on the same line, within the same clause. – OMG Ponies Feb 24 '10 at 16:42
@OMG Ponies: different definitions of clauses, I suppose. – brian Feb 24 '10 at 17:09

Carl Manaster · Answer 3 · 2010-02-24T00:25:39.417

1

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

edited Feb 24 '10 at 00:25

answered Feb 23 '10 at 23:01

Carl Manaster

39,912
17
102
155

Your first example is actually an inner join, just a hidden one (or rather tree). – user76035 Feb 23 '10 at 23:06
But then the only rows returned have `s1.sid = s2.sid = c1.sid = c2.sid` ... I suspect the "pairs" of `suppliers` he wants have different `sid` keys. – Joe Koberg Feb 23 '10 at 23:06
@Joe, you have a good point, although you're not correct. The join between the catalog tables is not on `sid`; it's on `pid`. The good point is that we should exclude pairs of identical catalogs; I'll fix that. Thanks. – Carl Manaster Feb 23 '10 at 23:22
Yes, and I'd just replace != with a < so we don't get symetric duplicates (Supplier1, Supplier2) and (Supplier2, Supplier1) – user76035 Feb 23 '10 at 23:46
Thanks, @wwosik; that's a better idea. Done. – Carl Manaster Feb 24 '10 at 00:26

score 0 · Answer 4 · answered Feb 23 '10 at 22:58

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

score 0 · Answer 5 · answered Feb 23 '10 at 23:04

0

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)

answered Feb 23 '10 at 23:04

user76035

1,526
1
10
12

Michael Buen · Accepted Answer · 2010-02-24T23:33:01.177

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

SQL: Having a table twice in the FROM clause

6 Answers6