Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    6

    Sub-select not allowed in JOIN clause

    When I execute some SQL similar to the following I get an error message saying that sub-selects are not supported on joins. I cannot work out whether there is a logical reason for this or it is just a limitation of my DBMS.
    Code:
    SELECT
        ....
    FROM
        t1
    LEFT JOIN
        t2
    ON
        t1.id = t2.id
    AND t2.foo = (SELECT
                      MIN(t3.foo)
                  FROM
                      t3
                  WHERE
                      t2.x = t3.y)
    I have only tried running this code on Ingres 9.2. I suspect that the Query Optimizer might just not be advanced to generate a Query Execution Plan for this level of nesting. However, there may be some logical reason why this is impossible that I am overlooking. Any thoughts?

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by zeolite
    When I execute some SQL similar to the following I get an error message saying that sub-selects are not supported on joins. I cannot work out whether there is a logical reason for this or it is just a limitation of my DBMS.Any thoughts?
    The columns you specify for an inner join are supposed to be columns the DBMS can use to match rows between tables. What you're trying to do is filter rows, so it should be in a WHERE clause. (Or a HAVING clause...)

    Keep in mind that any SELECT foo FROM a JOIN b ON a.x = b.x is simply syntactic sugar for SELECT foo FROM a CROSS JOIN b WHERE a.x = b.x. Recall that CROSS JOIN means "find all possible combinations of rows", but once you restrict the results to only those rows with a matching column, you get a regular inner join. That's why it works the way it does.

  3. #3
    Join Date
    Apr 2009
    Posts
    6
    Thanks for the info. That helps a lot

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •