Обсуждение: [GENERAL] SELECT statement with sub-queries
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
Вложения
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
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?
>
>
>
>
>
>
>
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
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?
>>
>>
>>
>>
>>
>>
>>
>
>
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
Вложения
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