SELECT statement with sub-queries

On Sun, May 28, 2017 at 10:33 PM, Michelle Konzack <linux4michelle@gmail.com

wrote:

Hello *,

I try to get columns from my database with a singel SELECT, but I stuck.

I have 3 tables like:

1) categories (serial,cat)

2) manufacturers (serial,m_name)

3) products (serial,category,manufacturer,p_name)

where the "category" and "manufacturer"
are numerical IDs from the two tables above.

So I like to replace the numerical IDs with the appropriated values,
mean

SELECT * FROM products WHERE category IN
(SELECT categories.cat FROM categories WHERE
categories.serial==products.category);

But I get:

ERROR: operator does not exist: integer==integer
LINE1: ...gories.cat FROM categories WHERE categories.serial==products.
category);
^
Hmm, "serial" is an "integer" and the "category" and "manufacturer" too.

So whats wrong with it?

--
Michelle Konzack Miila ITSystems @ TDnet
GNU/Linux Developer 00372-54541400

Wouldn't a simple join like the one below suffice:

Select a.*,b.cat from products as a, categories as b where
a.category=b.serial

or am I missing something?

Hello,

On Sun, 2017-05-28 at 20:03 +0300, Michelle Konzack wrote:

Hello *,

I try to get columns from my database with a singel SELECT, but I
stuck.

I have 3 tables like:

1)  categories (serial,cat)

2)  manufacturers (serial,m_name)

3)  products (serial,category,manufacturer,p_name)

where the "category" and "manufacturer"
are numerical IDs from the two tables above.

So I like to replace the numerical IDs with the appropriated values,
mean

SELECT * FROM products WHERE category IN 
    (SELECT categories.cat FROM categories WHERE 
    categories.serial==products.category);

But I get:

ERROR:  operator does not exist: integer==integer
LINE1: ...gories.cat FROM categories
WHERE  categories.serial==products.category);
                                                             ^
Hmm, "serial" is an "integer" and the "category" and "manufacturer"
too.

So whats wrong with it?

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);

Cheers,
Rob

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Michelle Konzack Miila ITSystems @ TDnet
GNU/Linux Developer 00372-54541400

Hallo,

SELECT *
FROM products
WHERE exists
(SELECT categories.cat FROM categories WHERE
categories.serial==products.category);
or
SELECT * FROM products
WHERE category IN
(SELECT categories.cat FROM categories);

On 28.05.2017 19:03, Michelle Konzack wrote:

Hello *,

I try to get columns from my database with a singel SELECT, but I stuck.

I have 3 tables like:

1) categories (serial,cat)

2) manufacturers (serial,m_name)

3) products (serial,category,manufacturer,p_name)

where the "category" and "manufacturer"
are numerical IDs from the two tables above.

So I like to replace the numerical IDs with the appropriated values,
mean

SELECT * FROM products WHERE category IN
(SELECT categories.cat FROM categories WHERE
categories.serial==products.category);

But I get:

ERROR: operator does not exist: integer==integer
LINE1: ...gories.cat FROM categories WHERE categories.serial==products.category);
^
Hmm, "serial" is an "integer" and the "category" and "manufacturer" too.

So whats wrong with it?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 28.05.2017 20:19, ml@ft-c.de wrote:

Hallo,

SELECT *
FROM products
WHERE exists
(SELECT categories.cat FROM categories WHERE
categories.serial==products.category);

categories.serial = products.category); -- one equal sign

or
SELECT * FROM products
WHERE category IN
(SELECT categories.cat FROM categories);

On 28.05.2017 19:03, Michelle Konzack wrote:

Hello *,

I try to get columns from my database with a singel SELECT, but I stuck.

I have 3 tables like:

1) categories (serial,cat)

2) manufacturers (serial,m_name)

3) products (serial,category,manufacturer,p_name)

where the "category" and "manufacturer"
are numerical IDs from the two tables above.

So I like to replace the numerical IDs with the appropriated values,
mean

SELECT * FROM products WHERE category IN
(SELECT categories.cat FROM categories WHERE
categories.serial==products.category);

But I get:

ERROR: operator does not exist: integer==integer
LINE1: ...gories.cat FROM categories WHERE
categories.serial==products.category);
^
Hmm, "serial" is an "integer" and the "category" and "manufacturer" too.

So whats wrong with it?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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;

This is not working

--
Michelle Konzack Miila ITSystems @ TDnet
GNU/Linux Developer 00372-54541400

On 2017-05-28 20:19:59 ml@ft-c.de hacked into the keyboard:

Hallo,

SELECT *
FROM products
WHERE exists
(SELECT categories.cat FROM categories WHERE
categories.serial==products.category);

This does not give an error but does nothing

or
SELECT * FROM products
WHERE category IN
(SELECT categories.cat FROM categories);

This give an error

See previously mail for what I want to archive

--
Michelle Konzack Miila ITSystems @ TDnet
GNU/Linux Developer 00372-54541400

On Sun, May 28, 2017 at 11:54 AM, Michelle Konzack <linux4michelle@gmail.com

wrote:

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"

​Then go and read the first response in this thread, ​Amitabh Kant's, and
confirm it works or say why it does not. Personally I prefer "products
JOIN categories ON/USING" but that is style and the "FROM products,
categories WHERE" formulation will give the correct answer.

The syntax error in your original message is, like others have said,
because "==(int, int)" is not a known operator.

And typically one writes: "EXISTS (correlated subquery)" instead of "IN
(correlated subquery)". But if you want to replace a column in the output
a predicate subquery (WHERE clause) that effectively implements a semi-join
(only return results from one table) will not help you since you cannot
actually refer to any of the columns in the subquery in the main query.
You need an actual join to do that. IOW, writing "FROM products WHERE"
when the output value you want is on the category table isn't going to help
you.

David J.

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.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general