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

From: Jean-Luc Lachance <jllachan(at)nsd(dot)ca>
To: Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: missing FROM-clause notice but nothing is missing ...
Date: 2003-03-28 16:23:18
Message-ID: 3E8476F6.36E431A@nsd.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message DeJuan Jackson 2003-03-28 16:25:16 System lockup
Previous Message Greg Stark 2003-03-28 16:11:52 Re: missing FROM-clause notice but nothing is missing ...