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

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.