CODEWITHSUNDEEP
mysqlsqlmysql-error-1064
jakebird451
jakebird451

Reputation: 2348

Too many parenthesis

I am trying to find the makers of two or more different computers (PC and/or laptop) with "HD" to be greater than or equal to 10.

Product has: maker & model

both PC & Laptop have: model & HD

This is what I have written so far:

SELECT DISTINCT maker
FROM
    (
        (
            SELECT model, maker
            FROM
            (SELECT model FROM laptop WHERE hd >= 10) AS lap2
            NATURAL JOIN product
        ) as ta
        UNION ALL
        (
            SELECT model, maker
            FROM
            (SELECT model FROM pc WHERE hd >= 10) AS pc2
            NATURAL JOIN product
        ) AS tb
    ) AS t1
    JOIN t1 as t2
    ON (t1.model != t2.model and t1.model > t2.model)

However, it provides me with lovely error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS tb ) AS t1 JOIN t1 as t2 ON (t1.model != t2.model and t1.model > t2.model)' at line 16

EDIT:

Working pieces:

SELECT model, maker
FROM
(SELECT model FROM laptop WHERE hd >= 10) AS lap2
NATURAL JOIN product

SELECT model, maker
FROM
(SELECT model FROM pc WHERE hd >= 10) AS pc2
NATURAL JOIN product

Upvotes: 1

Views: 682

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753735

Since your parentheses are balanced, I don't think 'too many parentheses' is the problem.

The trouble is at

JOIN t1 AS t2

You have t1 as the UNION query; you can't readily also have t1 as a table without confusing everyone.

You might be able to write:

 JOIN t3 AS t2

where t3 is a table (or view) in the DBMS.

Incidentally, your ON condition doesn't need both conditions:

 ON (t1.model != t2.model AND t1.model > t2.model)

It is sufficient to use:

ON (t1.model > t2.model)

Complex digression

If you want to do a self-join on the UNION, you will have to write the UNION out twice, or (if MySQL supported it, which I don't think it does) a CTE (common table expression), aka WITH clause before the main SQL statement:

WITH t1(model, maker) AS
    (SELECT model, maker
       FROM (SELECT model FROM laptop WHERE hd >= 10) AS lap2
       NATURAL JOIN product
     UNION ALL
     SELECT model, maker
       FROM (SELECT model FROM pc WHERE hd >= 10) AS pc2
       NATURAL JOIN product
    )
SELECT DISTINCT t1a.maker
  FROM t1 AS t1a
  JOIN t1 AS t1b ON (t1a.model > t1b.model)

I'm not quite sure what the ON condition means; it seems to favour the Zebras over the Aardvarks, but that's about all. However, that is syntactically (approximately) correct.

Without the WITH clause, you write the expression out twice:

SELECT DISTINCT t1a.maker
  FROM (SELECT model, maker
           FROM (SELECT model FROM laptop WHERE hd >= 10) AS lap2
           NATURAL JOIN product
         UNION ALL
         SELECT model, maker
           FROM (SELECT model FROM pc WHERE hd >= 10) AS pc2
           NATURAL JOIN product
       ) AS t1a
  JOIN (SELECT model, maker
           FROM (SELECT model FROM laptop WHERE hd >= 10) AS lap2
           NATURAL JOIN product
         UNION ALL
         SELECT model, maker
           FROM (SELECT model FROM pc WHERE hd >= 10) AS pc2
           NATURAL JOIN product
       ) AS t1b
    ON (t1a.model > t1b.model)

Simple answer

Of course, this was just an interpretation of your query. Your question seems much simpler:

SELECT Maker
  FROM (SELECT model, maker
           FROM (SELECT model FROM laptop WHERE hd >= 10) AS lap2
           NATURAL JOIN product
         UNION ALL
         SELECT model, maker
           FROM (SELECT model FROM pc WHERE hd >= 10) AS pc2
           NATURAL JOIN product
       )  AS t  -- per comment (and SQL standard, but it really is superfluous!)
 GROUP BY Maker
HAVING COUNT(*) > 1;

Upvotes: 3

Related Questions