From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Mike Christensen <mike(at)kitchenpc(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: What's wrong with this query? |
Date: | 2010-09-17 14:05:16 |
Message-ID: | 10102.1284732316@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Mike Christensen <mike(at)kitchenpc(dot)com> writes:
> Here's the query:
> SELECT R.RecipeId, R.Title, R.Description, R.ImageUrl, R.Rating,
> R.PrepTime, R.CookTime, R.OwnerId, U.Alias, ts_rank_cd(R.TextSearch,
> query) as Rank
> FROM Recipes R, plainto_tsquery('veggie burgers') query
> INNER JOIN Users U ON U.UserId = R.OwnerId
> WHERE (R.TextSearch @@ query)
> AND NOT EXISTS (select 1 from RecipeIngredients inner join
> IngredientBlacklist using (IngredientId) where RecipeId = r.RecipeId
> and IngredientBlacklist.UserId =
> 'affaa328-5b53-430e-991a-22674ede6faf' limit 1)
> ORDER BY Rank DESC LIMIT 100;
> Here's the error:
> ERROR: invalid reference to FROM-clause entry for table "r"
> LINE 3: INNER JOIN Users U ON U.UserId = R.OwnerId
You've been bitten by the mysql-ish idea that comma and JOIN are
interchangeable. They are not. In the SQL standard, and in every
implementation of it other than mysql, JOIN binds tighter than comma
--- so that INNER JOIN's condition can only refer to "query" and "u",
not "r". Try it like this (or any of several other ways):
FROM Recipes R INNER JOIN Users U ON U.UserId = R.OwnerId,
plainto_tsquery('veggie burgers') query
WHERE ...
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-09-17 14:13:40 | Re: where does postgres keep the query result until it is returned? |
Previous Message | Sam Mason | 2010-09-17 13:19:51 | Re: where does postgres keep the query result until it is returned? |