Re: [GENERAL] SELECT statement with sub-queries

On 05/28/2017 10:53 AM, Michelle Konzack wrote:
> On 2017-05-29 03:24:54 rob stone hacked into the keyboard:
>> You only need a single equals sign in SQL.
>>
>> SELECT * FROM products WHERE category IN
>>      (SELECT categories.cat FROM categories WHERE
>>      categories.serial = products.category);
>
> I have tried this too, but then I get:
>
> ERROR:  operator does not exist: integer = character varying
> LINE 1: SELECT * FROM products WHERE category IN (SELECT categories....
>                                                ^
>
> My sql file is:
>
> ----8<------------------------------------------------------------------
> DROP TABLE categories;
> DROP TABLE manufacturers;
> DROP TABLE products;
>
> CREATE TABLE categories (
>     serial        integer NOT NULL,
>     cat        varchar(40),
> );
>
> CREATE TABLE manufacturers (
>     serial        integer NOT NULL,
>     m_name        varchar(40),
>     m_address    varchar(200),
>     m_images    varchar(100),
>     m_desc        varchar(1000),
> );
>
> CREATE TABLE products (
>     serial        integer NOT NULL,
>     category    integer NOT NULL,
>     manufacturer    integer NOT NULL,
>     p_name        varchar(40),
>     p_images    varchar(100),
>     p_desc        varchar(10000),
> );
> ----8<------------------------------------------------------------------
>
> This is WHY I am puzzeling arround with the "integer" error.

Because you are comparing categories.cat 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;


>
>


--
Adrian Klaver
adrian.klaver@aklaver.com