From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Michelle Konzack <linux4michelle(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: SELECT statement with sub-queries |
Date: | 2017-05-28 20:15:57 |
Message-ID: | efaf1e34-30a4-40bc-0a51-68b03fc134d9@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 05/28/2017 11:54 AM, Michelle Konzack wrote:
> On 2017-05-28 11:23:47 Adrian Klaver hacked into the keyboard:
>> On 05/28/2017 10:53 AM, Michelle Konzack wrote:
>>>>
>>>> SELECT * FROM products WHERE category IN
>>>> (SELECT categories.cat FROM categories WHERE
>>>> categories.serial = products.category);
>
>> Because you are comparing categories.cat
>
> ehm no
Actually yes:
SELECT categories.cat FROM categories WHERE
categories.serial = products.category
is going to select categories.cat which is a varchar.
SELECT * FROM products WHERE category IN ...
is asking to select all fields from where the products.category field is
in the output of the above sub-select, which reduces down to
products.category = categories.cat
or
integer = varchar. As the error message says , that is not possible.
>
> I want to replace in the output the numerical ID from "products.category"
> with the value of "categories.cat", where the "products.category" match
> the "categories.serial"
>
>
>> which is a varchar to
>> products.category which is an integer. The above is crying out for
>> FOREIGN KEYS. For the time being I going to assume products.category
>> is a faux FK to categories.serial so;
>>
>> SELECT * FROM products WHERE products.category = categories.serial;
My mistake, it should be:
SELECT categories.cat, manufacturer, p_name, p_images, p_desc
FROM products, categories WHERE products.category = categories.serial;
>
> This is not working
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | elliot_rock | 2017-05-29 01:38:53 | pgAdmin4 - no Query tools available |
Previous Message | David G. Johnston | 2017-05-28 19:22:07 | Re: SELECT statement with sub-queries |