Re: [GENERAL] SELECT statement with sub-queries

[Adrian Klaver]

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

[David G. Johnston]

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

[Michelle Konzack]

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

[Michelle Konzack]

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

[ml]

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

[Adrian Klaver]

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

[ml]

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

[Michelle Konzack]

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

[rob stone]

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

[Amitabh Kant]

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

[Michelle Konzack]

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