From: | Mike Christensen <mike(at)kitchenpc(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: What's wrong with this query? |
Date: | 2010-09-22 07:26:43 |
Message-ID: | AANLkTikUuS=KgsAXBHBo_6WgF7LawkNkxAEwk8bbnDt=@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Tom, sorry to be a total n00b, but can you give me a few more pointers?
I've noticed if I swap the two "tables" around, like:
FROM plainto_tsquery('quiche') query, Recipes R
then it works..
So are you saying that a JOIN can only refer to itself and the last
table on the FROM list?
I actually hate multi-table FROM clauses (I never use them, which is
why I'm unfamiliar with this behavior. I'd love to just add a JOIN on
the plainto_tsquery function, however when I try that, it won't let me
refer to it on the SELECT clause thus I can't get the rank.. I guess
what I'm asking is what's the best way to write this particular query
:)
Mike
On Fri, Sep 17, 2010 at 7:05 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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 | Nagy Zoltan | 2010-09-22 10:58:53 | Re: versioned pl/pgsql functions |
Previous Message | Christopher Gorge A. Marges | 2010-09-22 07:05:37 | Re: Reclaiming space |