Re: [GENERAL] SELECT statement with sub-queries
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.kla...@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
Re: [GENERAL] SELECT statement with sub-queries
On Sun, May 28, 2017 at 11:54 AM, Michelle Konzack 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.
Re: [GENERAL] SELECT statement with sub-queries
On 2017-05-28 20:19:59 m...@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 KonzackMiila ITSystems @ TDnet GNU/Linux Developer 00372-54541400 signature.asc Description: Digital signature
Re: [GENERAL] SELECT statement with sub-queries
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 KonzackMiila ITSystems @ TDnet GNU/Linux Developer 00372-54541400 signature.asc Description: Digital signature
Re: [GENERAL] SELECT statement with sub-queries
On 28.05.2017 20:19, m...@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
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_imagesvarchar(100), m_desc varchar(1000), ); CREATE TABLE products ( serial integer NOT NULL, categoryinteger NOT NULL, manufacturerinteger NOT NULL, p_name varchar(40), p_imagesvarchar(100), p_desc varchar(1), ); 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.kla...@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
Re: [GENERAL] SELECT statement with sub-queries
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
Re: [GENERAL] SELECT statement with sub-queries
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_imagesvarchar(100), m_desc varchar(1000), ); CREATE TABLE products ( serial integer NOT NULL, categoryinteger NOT NULL, manufacturerinteger NOT NULL, p_name varchar(40), p_imagesvarchar(100), p_desc varchar(1), ); 8<-- This is WHY I am puzzeling arround with the "integer" error. -- Michelle KonzackMiila ITSystems @ TDnet GNU/Linux Developer 00372-54541400 signature.asc Description: Digital signature
Re: [GENERAL] SELECT statement with sub-queries
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
Re: [GENERAL] SELECT statement with sub-queries
On Sun, May 28, 2017 at 10:33 PM, 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? > > > > > > > > -- > Michelle KonzackMiila 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?
[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 KonzackMiila ITSystems @ TDnet GNU/Linux Developer 00372-54541400 signature.asc Description: Digital signature