Thread

1. missing FROM-clause notice but nothing is missing ...

   Jean-Christian Imbeault <jc@mega-bucks.co.jp> — 2003-03-28T02:29:43Z

   I get a "missing FROM-clause" with the following query. I don't see why 
   as prod_id is a FK in the supposedly missing table ...
   
   I also get an error on my ORDER by, even though I am ordering on 
   products.id which is what both selects retrieve ...
   
   This looks like a simple thing but I cannot figure out what I missed ....
   
   DB=# SELECT products.id
         FROM products
         WHERE name ILIKE 'AA'
   
         UNION
   
         SELECT prod_id
         FROM   rel_cast_crew_movies
         WHERE  cast_crew_id=1012
         ORDER BY products.id;
   
   NOTICE:  Adding missing FROM-clause entry for table "products"
   ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of 
   the result columns
   
   
   I even tried this convoluted query to get around the notice and error 
   but to no avail:
   
   
   DB=# SELECT products.id
         FROM products
         WHERE name ILIKE 'AA'
   
         UNION
   
         SELECT products.id
         FROM   rel_cast_crew_movies, products
         WHERE  cast_crew_id=1012
           AND  prod_id=products.id
         ORDER BY products.id;
   
   
   The tables:
   
   DB=# \d rel_cast_crew_movies
   Table "public.rel_cast_crew_movies"
        Column    |  Type   | Modifiers
   --------------+---------+-----------
     prod_id      | integer | not null
     cast_crew_id | integer | not null
   Indexes: rel_cast_crew_movies_pkey primary key btree (cast_crew_id, prod_id)
   Foreign Key constraints: $1 FOREIGN KEY (prod_id) REFERENCES 
   products(id) ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY 
   DEFERRED,
                             $2 FOREIGN KEY (cast_crew_id) REFERENCES 
   cast_crew(id) ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY 
   DEFERRED
   
   
   

2. Re: missing FROM-clause notice but nothing is missing ...

   Hadley Willan <hadley.willan@deeperdesign.co.nz> — 2003-03-28T04:44:49Z

   I think you'll find life easier if create a view then operate on that
   view.
   
   create view v_products_cast_crews AS
      SELECT p.id AS products,
             p.name AS product_name,
             cc.id AS cast_crew,
             cc.name AS cast_name
       FROM  rel_cast_crew_movies AS rccm
       LEFT JOIN products AS p ON p.id = rccm.prod_id
       LEFT JOIN cast_crew AS cc ON cc.id = rccm.cast_crew_id;
   
   Then you can do this;
   
    select * from v_products_cast_crews where product_name ILIKE 'AA' AND
   cast_crew = 1012 ORDER BY products;
   
   
   Cheers.
   
   On Fri, 2003-03-28 at 14:29, Jean-Christian Imbeault wrote:
   > I get a "missing FROM-clause" with the following query. I don't see why 
   > as prod_id is a FK in the supposedly missing table ...
   > 
   > I also get an error on my ORDER by, even though I am ordering on 
   > products.id which is what both selects retrieve ...
   > 
   > This looks like a simple thing but I cannot figure out what I missed ....
   > 
   > DB=# SELECT products.id
   >       FROM products
   >       WHERE name ILIKE 'AA'
   > 
   >       UNION
   > 
   >       SELECT prod_id
   >       FROM   rel_cast_crew_movies
   >       WHERE  cast_crew_id=1012
   >       ORDER BY products.id;
   > 
   > NOTICE:  Adding missing FROM-clause entry for table "products"
   > ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of 
   > the result columns
   > 
   > 
   > I even tried this convoluted query to get around the notice and error 
   > but to no avail:
   > 
   > 
   > DB=# SELECT products.id
   >       FROM products
   >       WHERE name ILIKE 'AA'
   > 
   >       UNION
   > 
   >       SELECT products.id
   >       FROM   rel_cast_crew_movies, products
   >       WHERE  cast_crew_id=1012
   >         AND  prod_id=products.id
   >       ORDER BY products.id;
   > 
   > 
   > The tables:
   > 
   > DB=# \d rel_cast_crew_movies
   > Table "public.rel_cast_crew_movies"
   >      Column    |  Type   | Modifiers
   > --------------+---------+-----------
   >   prod_id      | integer | not null
   >   cast_crew_id | integer | not null
   > Indexes: rel_cast_crew_movies_pkey primary key btree (cast_crew_id, prod_id)
   > Foreign Key constraints: $1 FOREIGN KEY (prod_id) REFERENCES 
   > products(id) ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY 
   > DEFERRED,
   >                           $2 FOREIGN KEY (cast_crew_id) REFERENCES 
   > cast_crew(id) ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY 
   > DEFERRED
   > 
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 6: Have you searched our list archives?
   > 
   > http://archives.postgresql.org
   -- 
   Hadley Willan > Systems Development > Deeper Design Limited. +64(7)377-3328 
   hadley.willan@deeperdesign.co.nz > www.deeperdesign.com > +64(21)-28-41-463
   Level 1, 4 Tamamutu St, PO Box 90, TAUPO 2730, New Zealand.
   
   
   

3. Re: missing FROM-clause notice but nothing is missing ...

   Jean-Christian Imbeault <jc@mega-bucks.co.jp> — 2003-03-28T04:47:09Z

   Hadley Willan wrote:
    >
    > I think you'll find life easier if create a view then operate on that
    > view.
   
   Ok, I'll try that. I've been avoiding views because I don't understand 
   them well or when/why to use them ...
   
   But I still am confused as to why postgres give me a notice and an 
   error. I don't see anything wrong with my query.
   
   Jean-Christian Imbeault
   
   
   

4. Re: missing FROM-clause notice but nothing is missing ...

   Jean-Christian Imbeault <jc@mega-bucks.co.jp> — 2003-03-28T04:58:52Z

   No go with the view ... same error:
   
   DB=# SELECT products.id
         FROM products
         WHERE name ILIKE 'AA'
   
         UNION
   
         SELECT prod_id
         FROM v_products_cast_crews
         WHERE cast_crew=1012
   
         ORDER BY products.id;
   
   NOTICE:  Adding missing FROM-clause entry for table "products"
   ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of 
   the result columns
   
   
   

5. Re: missing FROM-clause notice but nothing is missing ...

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-03-28T05:27:23Z

   On Fri, 28 Mar 2003, Jean-Christian Imbeault wrote:
   
   > I get a "missing FROM-clause" with the following query. I don't see why
   > as prod_id is a FK in the supposedly missing table ...
   >
   > I also get an error on my ORDER by, even though I am ordering on
   > products.id which is what both selects retrieve ...
   >
   > This looks like a simple thing but I cannot figure out what I missed ....
   >
   > DB=# SELECT products.id
   >       FROM products
   >       WHERE name ILIKE 'AA'
   >
   >       UNION
   >
   >       SELECT prod_id
   >       FROM   rel_cast_crew_movies
   >       WHERE  cast_crew_id=1012
   >       ORDER BY products.id;
   >
   > NOTICE:  Adding missing FROM-clause entry for table "products"
   > ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
   > the result columns
   
   I think this is because by the time you get to the order by, products and
   rel_cast_crew_movies are treated as being out of scope.  The column
   produced by the union is just named "id" I think.
   
   
   

6. Re: missing FROM-clause notice but nothing is missing ...

   Jean-Christian Imbeault <jc@mega-bucks.co.jp> — 2003-03-28T05:28:48Z

   Stephan Szabo wrote:
   > 
   > I think this is because by the time you get to the order by, products and
   > rel_cast_crew_movies are treated as being out of scope.  The column
   > produced by the union is just named "id" I think.
   
   You're right. changing the ORDER by products.id to simply ORDER by id 
   solved the problem ...
   
   I don't know much about SQL scoping but I would have hoped that a UNION 
   could have kept the fully-qualified column names (i.e. products.id 
   instead of simply ID).
   
   Jc
   
   
   

7. Re: missing FROM-clause notice but nothing is missing ...

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-03-28T05:46:15Z

   On Fri, 28 Mar 2003, Jean-Christian Imbeault wrote:
   
   > Stephan Szabo wrote:
   > >
   > > I think this is because by the time you get to the order by, products and
   > > rel_cast_crew_movies are treated as being out of scope.  The column
   > > produced by the union is just named "id" I think.
   >
   > You're right. changing the ORDER by products.id to simply ORDER by id
   > solved the problem ...
   >
   > I don't know much about SQL scoping but I would have hoped that a UNION
   > could have kept the fully-qualified column names (i.e. products.id
   > instead of simply ID).
   
   I believe the appropriate portion of the rules is (7.10 SR12b)
   
   1) Let C be the <column name> of the i-th column of T1. If
   the <column name> of the i-th column of T2 is C, then
   the <column name> of the i-th column of TR is C.
   
   2) Otherwise, the <column name> of the i-th column of TR is
   implementation-dependent and different from the <column
   name> of any column, other than itself, of any table
   referenced by any <table reference> contained in the
   SQL-statement.
   
   Note that AFAIK the column name reference above is just the column name
   not a qualified name.
   
   
   

8. Re: missing FROM-clause notice but nothing is missing ...

   Hadley Willan <hadley.willan@deeperdesign.co.nz> — 2003-03-28T06:03:22Z

   Jean-Christian Imbeault wrote:
   
   > No go with the view ... same error:
   >
   > DB=# SELECT products.id
   >      FROM products
   >      WHERE name ILIKE 'AA'
   >
   >      UNION
   >
   >      SELECT prod_id
   >      FROM v_products_cast_crews
   >      WHERE cast_crew=1012
   >
   >      ORDER BY products.id;
   >
   > NOTICE:  Adding missing FROM-clause entry for table "products"
   > ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of 
   > the result columns
   >
   Strange, I actually quickly whipped up the tables and it worked for me? 
   You did adjust some of the column names appropriately?
   Also, the LEFT JOIN's are best treated as a single line too.
   
   Anyway, I looked further in and I see you've got it working.
   
   Still, views are a more convenient way than doing manual UNIONs all the 
   time.
   
   H
   
   
   

9. Re: missing FROM-clause notice but nothing is missing ...

   Greg Stark <gsstark@mit.edu> — 2003-03-28T16:11:52Z

   Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
   
   > No go with the view ... same error:
   > 
   > DB=# SELECT products.id
   >       FROM products
   >       WHERE name ILIKE 'AA'
   > 
   >       UNION
   > 
   >       SELECT prod_id
   >       FROM v_products_cast_crews
   >       WHERE cast_crew=1012
   > 
   >       ORDER BY products.id;
   > 
   > NOTICE:  Adding missing FROM-clause entry for table "products"
   > ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the
   > result columns
   
   You could do "ORDER BY 1" to sort based on the first column, or you could just
   remove the word "products." in both places. It should work if you say "ORDER
   BY id". I would put AS id in the second select for clarity for the next reader
   though.
   
   Incidentally, are you sure you want UNION and not UNION ALL. The former has to
   exclude overlaps and that's a lot of extra work.
   
   -- 
   greg
   
   
   

10. Re: missing FROM-clause notice but nothing is missing ...

    Jean-Luc Lachance <jllachan@nsd.ca> — 2003-03-28T16:23:18Z

    Try:
    
    select id from (
    SELECT products.id as id
    FROM products
    WHERE name ILIKE 'AA'
    UNION
    SELECT prod_id as id
    FROM   rel_cast_crew_movies
    WHERE  cast_crew_id=1012
    ) as ss 
    ORDER BY id;
    
    
    
    Jean-Christian Imbeault wrote:
    > 
    > I get a "missing FROM-clause" with the following query. I don't see why
    > as prod_id is a FK in the supposedly missing table ...
    > 
    > I also get an error on my ORDER by, even though I am ordering on
    > products.id which is what both selects retrieve ...
    > 
    > This looks like a simple thing but I cannot figure out what I missed ....
    > 
    > DB=# SELECT products.id
    >       FROM products
    >       WHERE name ILIKE 'AA'
    > 
    >       UNION
    > 
    >       SELECT prod_id
    >       FROM   rel_cast_crew_movies
    >       WHERE  cast_crew_id=1012
    >       ORDER BY products.id;
    > 
    > NOTICE:  Adding missing FROM-clause entry for table "products"
    > ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
    > the result columns
    > 
    > I even tried this convoluted query to get around the notice and error
    > but to no avail:
    > 
    > DB=# SELECT products.id
    >       FROM products
    >       WHERE name ILIKE 'AA'
    > 
    >       UNION
    > 
    >       SELECT products.id
    >       FROM   rel_cast_crew_movies, products
    >       WHERE  cast_crew_id=1012
    >         AND  prod_id=products.id
    >       ORDER BY products.id;
    > 
    > The tables:
    > 
    > DB=# \d rel_cast_crew_movies
    > Table "public.rel_cast_crew_movies"
    >      Column    |  Type   | Modifiers
    > --------------+---------+-----------
    >   prod_id      | integer | not null
    >   cast_crew_id | integer | not null
    > Indexes: rel_cast_crew_movies_pkey primary key btree (cast_crew_id, prod_id)
    > Foreign Key constraints: $1 FOREIGN KEY (prod_id) REFERENCES
    > products(id) ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY
    > DEFERRED,
    >                           $2 FOREIGN KEY (cast_crew_id) REFERENCES
    > cast_crew(id) ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY
    > DEFERRED
    > 
    > ---------------------------(end of broadcast)---------------------------
    > TIP 6: Have you searched our list archives?
    > 
    > http://archives.postgresql.org
    
    
    

11. Re: missing FROM-clause notice but nothing is missing ...

    scott.marlowe <scott.marlowe@ihs.com> — 2003-03-28T18:48:09Z

    On Fri, 28 Mar 2003, Jean-Christian Imbeault wrote:
    
    > Stephan Szabo wrote:
    > > 
    > > I think this is because by the time you get to the order by, products and
    > > rel_cast_crew_movies are treated as being out of scope.  The column
    > > produced by the union is just named "id" I think.
    > 
    > You're right. changing the ORDER by products.id to simply ORDER by id 
    > solved the problem ...
    > 
    > I don't know much about SQL scoping but I would have hoped that a UNION 
    > could have kept the fully-qualified column names (i.e. products.id 
    > instead of simply ID).
    
    Not, that would actually be semantically incorrect.  The query you're 
    listing works kinda like this:
    
    (
        (SELECT products.id FROM products WHERE name ILIKE 'AA')
      UNION
        (SELECT prod_id FROM rel_cast_crew_movies WHERE  cast_crew_id=1012)
    )
    
    ORDER BY <fieldnamegoeshere>;
    
    The point I'm making is that when you union those two select statements, 
    the result fields CAN'T be called either products.id or 
    rel_cast_crew_movies.prod_id, because you've unioned those two datasets.  
    Both names would be incorrect.  So, the parser picks the unqualified name 
    of the first field to call the resultant field.  Note you can also use 
    order by <column number>:
    
    order by 1;
    
    It's a good idea to set force a new name so you know what you're getting:
    
    (
        (SELECT products.id as pid FROM products WHERE name ILIKE 'AA')
      UNION
        (SELECT prod_id FROM rel_cast_crew_movies WHERE cast_crew_id=1012)
    )
    
    ORDER BY pid;