subject:"\[GENERAL\] Query help"

Re: [GENERAL] Query help

2016-10-04 Thread Daniel Caldeweyher

Try this:

select distinct vendor_no, vendor_name
from ap_vendors
where vendor_no in (
   select vendor_no from ap_vendors
   group by vendor_no
   having array_agg(company_code) @> ARRAY['BUR','EBC','SNJ'])


On Wed, Oct 5, 2016 at 1:31 PM, Bret Stern  wrote:

> Good evening,
> I'm curious about a way to ask the following question of my vendors
> table.
>
> psuedo1 "select all vendors which exist in BUR and EBC and SNJ"
>
> and
> psuedo2 "select all vendors which DO NOT exist in all three show rooms
>
>
> The data is from a Sage accounting system which I pull out and place in a
> pg db. What we are trying to do is identify which vendors are defined in
> all three of our showrooms, and vice-versa, which ones are not.
>
> ap_vendors table
>   company_code character varying(10) NOT NULL,
>   ap_division_no character varying(2) NOT NULL,
>   vendor_no character varying(7) NOT NULL,
>   terms_code character varying(2),
>   vendor_name character varying(30),
>   address_line1 character varying(30),
>   address_line2 character varying(30),
>   address_line3 character varying(30),
>   city character varying(20),
>   state character varying(2),
>   zip_code character varying(10),
>   telephone_no character varying(17),
>   fax_no character varying(17),
>   CONSTRAINT ap_vendors_pk PRIMARY KEY (company_code, ap_division_no,
> vendor_no)
>
> sample records:
> "BUR";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";
> "92831";"''";"''"
> "BUR";"00";"AGORA";"30";"Agora Natural Surfaces";"''";"''";"''";"''";
> "''";"90248";"''";"''"
> "BUR";"00";"AKDOP";"30";"AKDO Pacific";"''";"''";"''";"''";"
> ''";"94545";"''";"''"
> "EBC";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";
> "92831";"''";"''"
> "EBC";"00";"AGORA";"30";"Agora Natural Surfaces";"''";"''";"''";"''";
> "''";"90248";"''";"''"
> "EBC";"00";"ARIZ01";"30";"Arizona Tile";"''";"''";"''";"''";"''"
> ;"94550";"''";"''"
> "SNJ";"00";"AKDOP";"30";"AKDO Pacific";"''";"''";"''";"''";"
> ''";"94545";"''";"''"
> "SNJ";"00";"AGORA";"30";"Agora Natural Surfaces";"''";"''";"''";"''";
> "''";"90248";"''";"''"
> "SNJ";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";
> "92831";"''";"''"
>
> What I need is a query which I can modify to return only vendors which
> exists
> in all three company_code columns ( BUR, EBC, SNJ) (they represent
> showroom location)
>
> eg; exists in BUR, EBC, SNJ
> ADEXU
> AGORA
>
> OR
>
> modify the query to return only the vendors which DO NOT exist in all
> three showrooms based on the first column company_code
>
> eg;
> AKDOP only exists in BUR and SNJ
> ARIZ01 only exists in EBC
>
> Thanks
> Bret
>
>
>

Re: [GENERAL] Query help

2016-10-04 Thread Rob Sargent


> On Oct 4, 2016, at 9:31 PM, Bret Stern  
> wrote:
> 
> Good evening,
> I'm curious about a way to ask the following question of my vendors
> table.
> 
> psuedo1 "select all vendors which exist in BUR and EBC and SNJ"
> 
> and
> psuedo2 "select all vendors which DO NOT exist in all three show rooms
> 
> 
> The data is from a Sage accounting system which I pull out and place in a
> pg db. What we are trying to do is identify which vendors are defined in
> all three of our showrooms, and vice-versa, which ones are not.
> 
> ap_vendors table
>   company_code character varying(10) NOT NULL,
>   ap_division_no character varying(2) NOT NULL,
>   vendor_no character varying(7) NOT NULL,
>   terms_code character varying(2),
>   vendor_name character varying(30),
>   address_line1 character varying(30),
>   address_line2 character varying(30),
>   address_line3 character varying(30),
>   city character varying(20),
>   state character varying(2),
>   zip_code character varying(10),
>   telephone_no character varying(17),
>   fax_no character varying(17),
>   CONSTRAINT ap_vendors_pk PRIMARY KEY (company_code, ap_division_no, 
> vendor_no)
> 
> sample records:
> "BUR";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";"92831";"''";"''"
> "BUR";"00";"AGORA";"30";"Agora Natural 
> Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''"
> "BUR";"00";"AKDOP";"30";"AKDO 
> Pacific";"''";"''";"''";"''";"''";"94545";"''";"''"
> "EBC";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";"92831";"''";"''"
> "EBC";"00";"AGORA";"30";"Agora Natural 
> Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''"
> "EBC";"00";"ARIZ01";"30";"Arizona 
> Tile";"''";"''";"''";"''";"''";"94550";"''";"''"
> "SNJ";"00";"AKDOP";"30";"AKDO 
> Pacific";"''";"''";"''";"''";"''";"94545";"''";"''"
> "SNJ";"00";"AGORA";"30";"Agora Natural 
> Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''"
> "SNJ";"00";"ADEXU";"30";"ADEX USA";"''";"''";"''";"''";"''";"92831";"''";"''"
> 
> What I need is a query which I can modify to return only vendors which exists
> in all three company_code columns ( BUR, EBC, SNJ) (they represent showroom 
> location)
> 
> eg; exists in BUR, EBC, SNJ
> ADEXU
> AGORA
> 
> OR
> 
> modify the query to return only the vendors which DO NOT exist in all
> three showrooms based on the first column company_code
> 
> eg; 
> AKDOP only exists in BUR and SNJ
> ARIZ01only exists in EBC
> 
> Thanks
> Bret
> 
> 
Not sure I like the schema but 
select vendor_no, count(*) from ap_vendors having count(*) = 3;

and maybe count(*) < 3 is your second answer.



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

[GENERAL] Query help

2016-10-04 Thread Bret Stern

Good evening,
I'm curious about a way to ask the following question of my vendors
table.

psuedo1 "select all vendors which exist in BUR and EBC and SNJ"

and
psuedo2 "select all vendors which DO NOT exist in all three show rooms


The data is from a Sage accounting system which I pull out and place in
a
pg db. What we are trying to do is identify which vendors are defined in
all three of our showrooms, and vice-versa, which ones are not.

ap_vendors table
  company_code character varying(10) NOT NULL,
  ap_division_no character varying(2) NOT NULL,
  vendor_no character varying(7) NOT NULL,
  terms_code character varying(2),
  vendor_name character varying(30),
  address_line1 character varying(30),
  address_line2 character varying(30),
  address_line3 character varying(30),
  city character varying(20),
  state character varying(2),
  zip_code character varying(10),
  telephone_no character varying(17),
  fax_no character varying(17),
  CONSTRAINT ap_vendors_pk PRIMARY KEY (company_code, ap_division_no,
vendor_no)

sample records:
"BUR";"00";"ADEXU";"30";"ADEX
USA";"''";"''";"''";"''";"''";"92831";"''";"''"
"BUR";"00";"AGORA";"30";"Agora Natural
Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''"
"BUR";"00";"AKDOP";"30";"AKDO
Pacific";"''";"''";"''";"''";"''";"94545";"''";"''"
"EBC";"00";"ADEXU";"30";"ADEX
USA";"''";"''";"''";"''";"''";"92831";"''";"''"
"EBC";"00";"AGORA";"30";"Agora Natural
Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''"
"EBC";"00";"ARIZ01";"30";"Arizona
Tile";"''";"''";"''";"''";"''";"94550";"''";"''"
"SNJ";"00";"AKDOP";"30";"AKDO
Pacific";"''";"''";"''";"''";"''";"94545";"''";"''"
"SNJ";"00";"AGORA";"30";"Agora Natural
Surfaces";"''";"''";"''";"''";"''";"90248";"''";"''"
"SNJ";"00";"ADEXU";"30";"ADEX
USA";"''";"''";"''";"''";"''";"92831";"''";"''"

What I need is a query which I can modify to return only vendors which
exists
in all three company_code columns ( BUR, EBC, SNJ) (they represent
showroom location)

eg; exists in BUR, EBC, SNJ
ADEXU
AGORA

OR

modify the query to return only the vendors which DO NOT exist in all
three showrooms based on the first column company_code

eg; 
AKDOP   only exists in BUR and SNJ
ARIZ01  only exists in EBC

Thanks
Bret

[GENERAL] Query help

2008-08-14 Thread novice

Hi,
I have a table

select id, config_id, start_day, end_day, start_time, end_time from config;

 id  | config_id | start_day | end_day | start_time | end_time
-+---+---+-++--
   1 |   101 | Mon   | Sun | 08:30:00   | 18:00:00
   2 |   101 | Mon   | Sun | 18:00:00   | 22:00:00
   3 |   555 | Mon   | Fri | 08:30:00   | 16:00:00



I'd like to write a query to generate the following... is it possible at all?

 config_id | day   | start_time | end_time
---+---+-+-
   101 | Mon   | 08:30:00   | 18:00:00
   101 | Mon   | 18:00:00   | 22:00:00
   101 | Tue   | 08:30:00   | 18:00:00
   101 | Tue   | 18:00:00   | 22:00:00
   101 | Wed   | 08:30:00   | 18:00:00
   101 | Wed   | 18:00:00   | 22:00:00
   101 | Thu   | 08:30:00   | 18:00:00
   101 | Thu   | 18:00:00   | 22:00:00
   101 | Fri   | 08:30:00   | 18:00:00
   101 | Fri   | 18:00:00   | 22:00:00
   101 | Sat   | 08:30:00   | 18:00:00
   101 | Sat   | 18:00:00   | 22:00:00
   101 | Sun   | 08:30:00   | 18:00:00
   101 | Sun   | 18:00:00   | 22:00:00
   555 | Mon   | 08:30:00   | 18:00:00
   555 | Tue   | 08:30:00   | 18:00:00
   555 | Wed   | 08:30:00   | 18:00:00
   555 | Thu   | 08:30:00   | 18:00:00
   555 | Fri   | 08:30:00   | 18:00:00

Thanks

-- 
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] Query help

2008-08-14 Thread Hui Xie

Hi ,

below can work?
select config_id, start_day as day, start_time, end_time from config
union
select config_id, end_day as day, start_time, end_time from config


Best Regards,
Hui Xie
---
Axisoft Co. Ltd. Zhuhai Branch
Tel: (86) 0756-3612121 8858 



novice [EMAIL PROTECTED] 
Sent by: [EMAIL PROTECTED]
15/08/2008 08:32 AM

To
pgsql-general@postgresql.org
cc

Subject
[GENERAL] Query help






Hi,
I have a table

select id, config_id, start_day, end_day, start_time, end_time from 
config;

 id  | config_id | start_day | end_day | start_time | end_time
-+---+---+-++--
   1 |   101 | Mon   | Sun | 08:30:00   | 18:00:00
   2 |   101 | Mon   | Sun | 18:00:00   | 22:00:00
   3 |   555 | Mon   | Fri | 08:30:00   | 16:00:00



I'd like to write a query to generate the following... is it possible at 
all?

 config_id | day   | start_time | end_time
---+---+-+-
   101 | Mon   | 08:30:00   | 18:00:00
   101 | Mon   | 18:00:00   | 22:00:00
   101 | Tue   | 08:30:00   | 18:00:00
   101 | Tue   | 18:00:00   | 22:00:00
   101 | Wed   | 08:30:00   | 18:00:00
   101 | Wed   | 18:00:00   | 22:00:00
   101 | Thu   | 08:30:00   | 18:00:00
   101 | Thu   | 18:00:00   | 22:00:00
   101 | Fri   | 08:30:00   | 18:00:00
   101 | Fri   | 18:00:00   | 22:00:00
   101 | Sat   | 08:30:00   | 18:00:00
   101 | Sat   | 18:00:00   | 22:00:00
   101 | Sun   | 08:30:00   | 18:00:00
   101 | Sun   | 18:00:00   | 22:00:00
   555 | Mon   | 08:30:00   | 18:00:00
   555 | Tue   | 08:30:00   | 18:00:00
   555 | Wed   | 08:30:00   | 18:00:00
   555 | Thu   | 08:30:00   | 18:00:00
   555 | Fri   | 08:30:00   | 18:00:00

Thanks

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


*** The email has been scanned by AxiScan ***

Re: [GENERAL] Query help

2008-08-14 Thread novice

2008/8/15 novice [EMAIL PROTECTED]:
 Hi,
 I have a table

 select id, config_id, start_day, end_day, start_time, end_time from config;

  id  | config_id | start_day | end_day | start_time | end_time
 -+---+---+-++--
   1 |   101 | Mon   | Sun | 08:30:00   | 18:00:00
   2 |   101 | Mon   | Sun | 18:00:00   | 22:00:00
   3 |   555 | Mon   | Fri | 08:30:00   | 16:00:00



 I'd like to write a query to generate the following... is it possible at all?

  config_id | day   | start_time | end_time
 ---+---+-+-
   101 | Mon   | 08:30:00   | 18:00:00
   101 | Mon   | 18:00:00   | 22:00:00
   101 | Tue   | 08:30:00   | 18:00:00
   101 | Tue   | 18:00:00   | 22:00:00
   101 | Wed   | 08:30:00   | 18:00:00
   101 | Wed   | 18:00:00   | 22:00:00
   101 | Thu   | 08:30:00   | 18:00:00
   101 | Thu   | 18:00:00   | 22:00:00
   101 | Fri   | 08:30:00   | 18:00:00
   101 | Fri   | 18:00:00   | 22:00:00
   101 | Sat   | 08:30:00   | 18:00:00
   101 | Sat   | 18:00:00   | 22:00:00
   101 | Sun   | 08:30:00   | 18:00:00
   101 | Sun   | 18:00:00   | 22:00:00
   555 | Mon   | 08:30:00   | 18:00:00
   555 | Tue   | 08:30:00   | 18:00:00
   555 | Wed   | 08:30:00   | 18:00:00
   555 | Thu   | 08:30:00   | 18:00:00
   555 | Fri   | 08:30:00   | 18:00:00

 Thanks


Solved:

 create TABLE weekday
(
wd  varchar(3),
seq int
)


INSERT INTO weekday (wd, seq) VALUES
('Mon', '1'),
('Tue', '2'),
('Wed', '3'),
('Thu', '4'),
('Fri', '5'),
('Sat', '6'),
('Sun', '7');

SELECT  config.config_id, w.wd, config.start_time, config.end_time
FROMconfig
INNER JOIN weekday sON  config.start_day= s.wd
INNER JOIN weekday eON  config.end_day  = e.wd
CROSS JOIN weekday w
WHERE   w.seq   = s.seq
AND w.seq   = e.seq
ORDER BY config.config_id, w.seq, w.wd

-- 
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] Query help

2008-08-14 Thread Brent Wood

If I read this correctly, you want the output sorted by 
config_id,start_day(day),start_time,

thus:

select config_id, start_day as day, start_time, end_time from config
order by config_id, start_day, start_time;


Cheers,

  Brent Wood

 novice [EMAIL PROTECTED] 08/15/08 3:55 PM 
Hi,
I have a table

select id, config_id, start_day, end_day, start_time, end_time from config;

 id  | config_id | start_day | end_day | start_time | end_time
-+---+---+-++--
   1 |   101 | Mon   | Sun | 08:30:00   | 18:00:00
   2 |   101 | Mon   | Sun | 18:00:00   | 22:00:00
   3 |   555 | Mon   | Fri | 08:30:00   | 16:00:00



I'd like to write a query to generate the following... is it possible at all?

 config_id | day   | start_time | end_time
---+---+-+-
   101 | Mon   | 08:30:00   | 18:00:00
   101 | Mon   | 18:00:00   | 22:00:00
   101 | Tue   | 08:30:00   | 18:00:00
   101 | Tue   | 18:00:00   | 22:00:00
   101 | Wed   | 08:30:00   | 18:00:00
   101 | Wed   | 18:00:00   | 22:00:00
   101 | Thu   | 08:30:00   | 18:00:00
   101 | Thu   | 18:00:00   | 22:00:00
   101 | Fri   | 08:30:00   | 18:00:00
   101 | Fri   | 18:00:00   | 22:00:00
   101 | Sat   | 08:30:00   | 18:00:00
   101 | Sat   | 18:00:00   | 22:00:00
   101 | Sun   | 08:30:00   | 18:00:00
   101 | Sun   | 18:00:00   | 22:00:00
   555 | Mon   | 08:30:00   | 18:00:00
   555 | Tue   | 08:30:00   | 18:00:00
   555 | Wed   | 08:30:00   | 18:00:00
   555 | Thu   | 08:30:00   | 18:00:00
   555 | Fri   | 08:30:00   | 18:00:00

Thanks

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


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

[GENERAL] query help

2007-09-13 Thread volunteer

hello

table is
+---+---+--+---+ 
|  id   |  one  | two  | three | 
+---+---+--+---+ 
| first | Jack  | Jill | Mary   | 
| last  | Ja | Ji  | Ma | 
+---+---+--+---+ 

result is
++---+---+
| id | one | two |
++---+---+ 
| first | Jack  | Jill |
| last | Ja  | Ji   |
++---+---+

query is??

sincerely 
siva


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] query help

2007-09-13 Thread Alexander Staubo

On 9/13/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 hello

 table is
 +---+---+--+---+
 |  id   |  one  | two  | three |
 +---+---+--+---+
 | first | Jack  | Jill | Mary   |
 | last  | Ja | Ji  | Ma |
 +---+---+--+---+

 result is
 ++---+---+
 | id | one | two |
 ++---+---+
 | first | Jack  | Jill |
 | last | Ja  | Ji   |
 ++---+---+

 query is??

Unless I am missing something crucial, this is SQL 101:

  select id, one, two from foo;

Alexander.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] query help

2007-09-13 Thread Rodrigo De León

On 9/13/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 query is??

http://www.w3schools.com/sql/default.asp

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] query help

2007-09-13 Thread volunteer

hello
i add more column not row for new user. i want all last like 'J%'.
http://www.nabble.com/an-other-provokative-question---tf4394285.html
sincerely
siva

 Original Message 
Subject: Re: [GENERAL] query help
From: Alexander Staubo [EMAIL PROTECTED]
Date: Thu, September 13, 2007 11:17 am
To: [EMAIL PROTECTED] [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org

On 9/13/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 hello

 table is
 +---+---+--+---+
 | id | one | two | three |
 +---+---+--+---+
 | first | Jack | Jill | Mary |
 | last | Ja | Ji | Ma |
 +---+---+--+---+

 result is
 ++---+---+
 | id | one | two |
 ++---+---+
 | first | Jack | Jill |
 | last | Ja | Ji |
 ++---+---+

 query is??

Unless I am missing something crucial, this is SQL 101:

select id, one, two from foo;

Alexander.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] query help

2007-09-13 Thread Alexander Staubo

On 9/13/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 i add more column not row for new user. i want all last like 'J%'.
 http://www.nabble.com/an-other-provokative-question---tf4394285.html

Sorry, but the only difference between your table example and your
result example was the absence, in the results, of one of the columns.
If you want to constrain by some attribute, then you have to tell us
that. I recommend that you buy a book on SQL. Lastly, I don't see what
this has to do with the provokative question thread.

Alexander.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] query help

2007-09-13 Thread Steve Crawford

[EMAIL PROTECTED] wrote:
 hello
 i add more column not row for new user. i want all last like 'J%'.
 http://www.nabble.com/an-other-provokative-question---tf4394285.html
 sincerely
 siva

You add a new _column_ for each user?!? That is hideously broken in so
many ways. It makes the trivially easy query you are trying to write
rather complicated, prevents you from being able to use indexes for
either constraints or performance and requires you to change your table
definition anytime you add data. And that's just for starters.

Fix your table so it has three columns (id, first, last). Then your
query is as easy as:
select id,first,last from foo where last like 'J%';

Cheers,
Steve


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] query help

2007-09-13 Thread volunteer

many apologees. right link
http://archives.postgresql.org/pgsql-general/2007-09/msg00607.php
i flip row to column if ok. but howto query??
sincerely
siva

 Original Message 
Subject: Re: [GENERAL] query help
From: Alexander Staubo [EMAIL PROTECTED]
Date: Thu, September 13, 2007 11:38 am
To: [EMAIL PROTECTED] [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org

On 9/13/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 i add more column not row for new user. i want all last like 'J%'.
 http://www.nabble.com/an-other-provokative-question---tf4394285.html

Sorry, but the only difference between your table example and your
result example was the absence, in the results, of one of the columns.
If you want to constrain by some attribute, then you have to tell us
that. I recommend that you buy a book on SQL. Lastly, I don't see what
this has to do with the provokative question thread.

Alexander.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] query help

2007-09-13 Thread brian


[EMAIL PROTECTED] wrote:

hello
i add more column not row for new user. i want all last like 'J%'.


I get the feeling that the result as you've laid it out is not what we 
all think it is. For example:


table is
+---+---+--+---+
| id | one | two | three |
+---+---+--+---+
| first | Jack | Jill | Mary |
| last | Ja | Ji | Ma |
+---+---+--+---+

I took that to meant that you have columns 'id', 'one', two', three', 
and that 'first'  'last' are field values. However, it now seems that 
'first'  'last' are column names. If so, this makes no sense. I think 
what you wanted to give us was:


+---+---+--+
|   id  | first | last |
+---+---+--+
|  one  |  Jack |  Ja  |
|  two  |  Jill |  Ji  |
| three |  Mary |  Ma  |

result:

+---+---+--+
|   id  | first | last |
+---+---+--+
|  one  |  Jack |  Ja  |
|  two  |  Jill |  Ji  |

So, the query you want is, in fact:

SELECT * FROM your_table WHERE last LIKE ('J%');

If that's not working for you, it's perhaps because you have rows for 
columns and columns for rows.



http://www.nabble.com/an-other-provokative-question---tf4394285.html
sincerely
siva



What the heck does this have to do with anything?

Please don't top-post.

brian

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] query help

2007-09-13 Thread volunteer

can u refer to row?? howto select * from table where row(#2) like 'J%'??
i wanted to test column storing but not ok as no row refer name/id.
many thank yous
sincerely
siva

 Original Message 
Subject: Re: [GENERAL] query help
From: [EMAIL PROTECTED]
Date: Thu, September 13, 2007 11:46 am
To: Alexander Staubo [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org

many apologees. right link
http://archives.postgresql.org/pgsql-general/2007-09/msg00607.php
i flip row to column if ok. but howto query??
sincerely
siva

 Original Message 
Subject: Re: [GENERAL] query help
From: Alexander Staubo [EMAIL PROTECTED]
Date: Thu, September 13, 2007 11:38 am
To: [EMAIL PROTECTED] [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org

On 9/13/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 i add more column not row for new user. i want all last like 'J%'.
 http://www.nabble.com/an-other-provokative-question---tf4394285.html

Sorry, but the only difference between your table example and your
result example was the absence, in the results, of one of the columns.
If you want to constrain by some attribute, then you have to tell us
that. I recommend that you buy a book on SQL. Lastly, I don't see what
this has to do with the provokative question thread.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] query help

2007-08-14 Thread Kirk Wythers

I need some help with rewriting a query. I have a fairly complicated  
query (for me anyway) that dumps daily climate data, filling in  
missing data with monthly averages (one line per day).


I want to output monthly averages (one line per month). I am having a  
hard time wrapping my head around this. Particularly how to deal with  
the doy column (day of year). I have tried several approaches and my  
forehead is starting to get my keyboard bloody.


Thanks in advance for any suggestions.

Here is the daily query:

SELECT CASE
WHEN w.station_id = site_near.station_id THEN w.obs_id
ELSE s.obs_id
END AS obs_id,
site_near.station_id,
site_near.longname,
w.year,
w.doy,
--replace missing values (-999) with the monthly average
   CASE w.tmax
 WHEN -999 THEN avgtmax.avg
 ELSE w.tmax
   END,
CASE w.tmin
 WHEN -999 THEN avgtmin.avg
 ELSE w.tmin
   END,
CASE s.par
 WHEN -999 THEN avgpar.avg
 ELSE s.par
   END,
CASE w.precip
 WHEN -999 THEN avgprecip.avg
 ELSE w.precip
   END
FROM  site_near
   INNER JOIN solar s
 ON (site_near.ref_solar_station_id = s.station_id
 AND site_near.obs_year = s.year)
   INNER JOIN weather w
 ON (site_near.ref_weather_station_id = w.station_id
 AND site_near.obs_year = w.year
 AND s.date = w.date)
   INNER JOIN (SELECT   MONTH,
round(avg(tmax)::numeric, 2) AS avg
   FROM weather
   WHEREtmax != -999
   GROUP BY MONTH) AS avgtmax
 ON (w.month = avgtmax.month)
INNER JOIN (SELECT   MONTH,
round(avg(tmin)::numeric, 2) AS avg
   FROM weather
   WHEREtmin != -999
   GROUP BY MONTH) AS avgtmin
 ON (w.month = avgtmin.month)
   INNER JOIN (SELECT   MONTH,
round(avg(par)::numeric, 2) AS avg
   FROM solar
   WHEREpar != -999
   GROUP BY MONTH) AS avgpar
 ON (s.month = avgpar.month)
INNER JOIN (SELECT   MONTH,
round(avg(precip)::numeric, 2) AS avg
   FROM weather
   WHEREprecip != -999
   GROUP BY MONTH) AS avgprecip
 ON (w.month = avgprecip.month)
--select station to output climate data by id number
WHERE  w.station_id = 219101

Re: [GENERAL] query help

2007-08-14 Thread Josh Tolley

On 8/14/07, Kirk Wythers [EMAIL PROTECTED] wrote:

 I need some help with rewriting a query. I have a fairly complicated query
 (for me anyway) that dumps daily climate data, filling in missing data with
 monthly averages (one line per day).

 I want to output monthly averages (one line per month). I am having a hard
 time wrapping my head around this. Particularly how to deal with the doy
 column (day of year). I have tried several approaches and my forehead is
 starting to get my keyboard bloody.

I think this came up on IRC today, so perhaps this is only for the
archives' sake, but you want to do something like this:

Assuming you have a table as follows:

CREATE TABLE climate_data (
   measurement_time  timestamp,
   measurement_value  integer);

...and you insert data into it regularly, you can get the average
measurement over a period of time with date_trunc(), which will
truncate a date or timestamp value to match whatever precision you
specify. For example, see the following:

eggyknap=# select date_trunc('month', now());
   date_trunc

 2007-08-01 00:00:00-06
(1 row)

Note: the -06 at the end means I'm in mountain time.

So if you want to get the average measurement over a month's time, you
need to do something like this:

SELECT DATE_TRUNC('MONTH', measurement_time), AVG(measurement_value)
FROM climate_data GROUP BY DATE_TRUNC('MONTH', measurement_time);

This will chop all the measurement_time values down to the month the
measurement was taken in, put all measurements in groups based on the
resulting value, and take the average measurement_value from each
group.

- Josh

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Query help

2007-03-08 Thread Richard Huxton


Madison Kelly wrote:

Hi all,

  I've got a query that looks through a table I use for my little search 
engine. It's something of a reverse-index but not quite, where a proper 
reverse index would have 'word | doc1, doc3, doc4, doc7' showing all the 
docs the keyword is in, mine has an entry for eac


  I've got a query like:

SELECT
sch_id, sch_for_table, sch_ref_id, sch_instances
FROM
search_index
WHERE
(sch_keyword LIKE '%digi%' OR sch_keyword LIKE '%madi%')
AND
sch_for_table!='client'
AND
... (more restrictions)
ORDER BY
sch_instances DESC;

  This returns references to a data column (sch_ref_id) in a given table 
(sch_for_table) for each matched keyword.


  The problem I am having is that two keywords might reference the same 
table/column which would, in turn, give me two+ search results pointing 
to the same entry.


  What I would like to do is, when two or more results match the same 
'sch_ref_id' and 'sch_for_table' to merge the results. Specifically, the 
'sch_instances' column is the number of times the given keyword is found 
in the table/column. I'd like to add up the number in the duplicate 
results (to give it a higher accuracy and move it up the search results).


You'll want something like:

SELECT
sch_id, sch_for_table, sch_ref_id,
SUM(sch_instances) AS tot_instances
...
GROUP BY
sch_id, sch_for_table, sch_ref_id
ORDER BY
tot_instances DESC;

The key word to search the manuals on is aggregates (sum(), count() etc).

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Query help

2007-03-08 Thread Madison Kelly


Richard Huxton wrote:

Madison Kelly wrote:

Hi all,

  I've got a query that looks through a table I use for my little 
search engine. It's something of a reverse-index but not quite, where 
a proper reverse index would have 'word | doc1, doc3, doc4, doc7' 
showing all the docs the keyword is in, mine has an entry for eac


  I've got a query like:

SELECT
sch_id, sch_for_table, sch_ref_id, sch_instances
FROM
search_index
WHERE
(sch_keyword LIKE '%digi%' OR sch_keyword LIKE '%madi%')
AND
sch_for_table!='client'
AND
... (more restrictions)
ORDER BY
sch_instances DESC;

  This returns references to a data column (sch_ref_id) in a given 
table (sch_for_table) for each matched keyword.


  The problem I am having is that two keywords might reference the 
same table/column which would, in turn, give me two+ search results 
pointing to the same entry.


  What I would like to do is, when two or more results match the same 
'sch_ref_id' and 'sch_for_table' to merge the results. Specifically, 
the 'sch_instances' column is the number of times the given keyword is 
found in the table/column. I'd like to add up the number in the 
duplicate results (to give it a higher accuracy and move it up the 
search results).


You'll want something like:

SELECT
sch_id, sch_for_table, sch_ref_id,
SUM(sch_instances) AS tot_instances
...
GROUP BY
sch_id, sch_for_table, sch_ref_id
ORDER BY
tot_instances DESC;

The key word to search the manuals on is aggregates (sum(), count() etc).



This is *exactly* the pointer I needed, thank you!

Sad thing is that I even used GROUP BY before... had just forgotten 
about it. ^_^;


Madison

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[GENERAL] Query help

2007-03-07 Thread Madison Kelly


Hi all,

  I've got a query that looks through a table I use for my little 
search engine. It's something of a reverse-index but not quite, where a 
proper reverse index would have 'word | doc1, doc3, doc4, doc7' showing 
all the docs the keyword is in, mine has an entry for eac


  I've got a query like:

SELECT
sch_id, sch_for_table, sch_ref_id, sch_instances
FROM
search_index
WHERE
(sch_keyword LIKE '%digi%' OR sch_keyword LIKE '%madi%')
AND
sch_for_table!='client'
AND
... (more restrictions)
ORDER BY
sch_instances DESC;

  This returns references to a data column (sch_ref_id) in a given 
table (sch_for_table) for each matched keyword.


  The problem I am having is that two keywords might reference the same 
table/column which would, in turn, give me two+ search results pointing 
to the same entry.


  What I would like to do is, when two or more results match the same 
'sch_ref_id' and 'sch_for_table' to merge the results. Specifically, the 
'sch_instances' column is the number of times the given keyword is found 
in the table/column. I'd like to add up the number in the duplicate 
results (to give it a higher accuracy and move it up the search results).


  Is this possible or would I need to add this logic in my program? I'd 
rather do it in PostgreSQL though, if I could.


  Here is the 'search_index' table I am using:

db= \d search_index
   Table public.search_index
Column |  Type   |   Modifiers
---+-+---
 sch_id| integer | not null default nextval('sch_seq'::regclass)
 sch_keyword   | text| not null
 sch_instances | integer | not null default 1
 sch_for_table | text| not null
 sch_ref_id| integer | not null
Indexes:
search_index_pkey PRIMARY KEY, btree (sch_id)

  Thanks in advance to any help you might be able to give me!

Madison

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Query help

2007-03-07 Thread Shaun Johnston


Try

SELECT DISTINCT

rather than SELECT

That should return a result with unique records.

Madison Kelly wrote:

Hi all,

  I've got a query that looks through a table I use for my little 
search engine. It's something of a reverse-index but not quite, where 
a proper reverse index would have 'word | doc1, doc3, doc4, doc7' 
showing all the docs the keyword is in, mine has an entry for eac


  I've got a query like:

SELECT
sch_id, sch_for_table, sch_ref_id, sch_instances
FROM
search_index
WHERE
(sch_keyword LIKE '%digi%' OR sch_keyword LIKE '%madi%')
AND
sch_for_table!='client'
AND
... (more restrictions)
ORDER BY
sch_instances DESC;

  This returns references to a data column (sch_ref_id) in a given 
table (sch_for_table) for each matched keyword.


  The problem I am having is that two keywords might reference the 
same table/column which would, in turn, give me two+ search results 
pointing to the same entry.


  What I would like to do is, when two or more results match the same 
'sch_ref_id' and 'sch_for_table' to merge the results. Specifically, 
the 'sch_instances' column is the number of times the given keyword is 
found in the table/column. I'd like to add up the number in the 
duplicate results (to give it a higher accuracy and move it up the 
search results).


  Is this possible or would I need to add this logic in my program? 
I'd rather do it in PostgreSQL though, if I could.


  Here is the 'search_index' table I am using:

db= \d search_index
   Table public.search_index
Column |  Type   |   Modifiers
---+-+---
 sch_id| integer | not null default nextval('sch_seq'::regclass)
 sch_keyword   | text| not null
 sch_instances | integer | not null default 1
 sch_for_table | text| not null
 sch_ref_id| integer | not null
Indexes:
search_index_pkey PRIMARY KEY, btree (sch_id)

  Thanks in advance to any help you might be able to give me!

Madison

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Query Help

2007-01-21 Thread Howard Cole


Tom Lane wrote:

It looks like you have a stale plan for that ON DELETE SET NULL
constraint.  Was there perhaps an index on operator_id that you removed?
Postgres is not very good about flushing cached plans when you change
table schemas (something I hope will be fixed in 8.3).

If that is the problem, starting a fresh session would be enough to take
care of it.

regards, tom lane

  


Thanks Tom. You were, as usual, correct!

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Query Help

2007-01-19 Thread Howard Cole


Hi All,

I am getting an error I do not understand from the following setup

CREATE TABLE timesheet_booking
(
 timesheet_booking_id bigserial NOT NULL,
 operator_id integer,
 booking_item_id integer,
 day date NOT NULL,
 minutes integer NOT NULL,
 CONSTRAINT timesheet_booking_pkey PRIMARY KEY (timesheet_booking_id),
 CONSTRAINT timesheet_booking_booking_item_id_fkey FOREIGN KEY 
(booking_item_id)

 REFERENCES booking_item (booking_item_id) MATCH SIMPLE
 ON UPDATE NO ACTION ON DELETE CASCADE,
 CONSTRAINT timesheet_booking_operator_id_fkey FOREIGN KEY (operator_id)
 REFERENCES operator (operator_id) MATCH SIMPLE
 ON UPDATE NO ACTION ON DELETE SET NULL
)
WITHOUT OIDS;


CREATE UNIQUE INDEX timesheet_booking_unique_idx
 ON timesheet_booking
 USING btree
 (operator_id, booking_item_id, day);

Now the timesheet booking contains the following data:

timesheet_booking_id, operator_id,booking_item_id,day,minutes
2;284;1;2007-01-18;10
4;284;2;2007-01-18;10

If I try the following query:
   delete from operator where operator_id=283;
I get the following error message:

ERROR: could not open relation with OID 438427
SQL state: XX000
Context: SQL statement UPDATE ONLY public.timesheet_booking SET 
operator_id = NULL WHERE operator_id = $1


If I try this sql statement directly it works OK. What does this error 
mean and how can I get rid of it?


Thanks
Howard.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Query Help

2007-01-19 Thread Tom Lane

Howard Cole [EMAIL PROTECTED] writes:
 If I try the following query:
 delete from operator where operator_id=283;
 I get the following error message:

 ERROR: could not open relation with OID 438427
 SQL state: XX000
 Context: SQL statement UPDATE ONLY public.timesheet_booking SET 
 operator_id = NULL WHERE operator_id = $1

It looks like you have a stale plan for that ON DELETE SET NULL
constraint.  Was there perhaps an index on operator_id that you removed?
Postgres is not very good about flushing cached plans when you change
table schemas (something I hope will be fixed in 8.3).

If that is the problem, starting a fresh session would be enough to take
care of it.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] query help

2001-09-14 Thread Ian Barwick


Jeff Patterson wrote:

 This seems like such a basic function that I'm sure I am missing something
 fundamental. I have a table, say xref, whose columns are primary key
 values for other tables. Some of theses keys may be NULL for a given row
 in xref. I want to create a query that returns the corresponding entries
 in the other tables.
 
 xref:
 drug_id | function_id|syst_id |text_id
 --
 d0001   | 2  |   3| 3423
 d0001   | 5  || 5678
 d0056   | 3  |   5|
 
 system_id:
 syst_id | syst_desc
 ---
 3   | renal
 4   | hepatic
 5   |  respiratory
 
 clinical_text:
 text_id| clinical_text
 ---
 3423   | 'some medical mumbo jumbo'
 5678   | 'more of the same'
 
 I want the syst_desc and clinical_text (plus other similar data from
 tables not shown) given a drug_id and function_id.
 
 Any help?

If I understand you correctly, you want the query to return the syst_desc 
and clinical_text fields where either or both are available? (and not just 
where both are available)?

If so a LEFT JOIN may be your best friend. The statement could look like 
this:

SELECT system_id.syst_desc, 
   clinical_text.clinical_text
  FROM xref
 LEFT JOIN system_id
ON xref.syst_id=system_id.syst_id
 LEFT JOIN clinical_text
ON xref.text_id=clinical_text.text_id
 WHERE xref.drug_id=?   - insert query value here
   AND xref.function_id=?   - and here

(disclaimer: statement untested)

The values not available will be returned as NULL.

 Thanks a bunch,

a bunch of what? ;-)


HTH

Ian Barwick

-- 
Ian Barwick - Developer
http://www.akademie.de

Remove SUNGLASSES to reply ;-)

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] query help

2001-09-07 Thread Jeff Patterson


This seems like such a basic function that I'm sure I am missing something
fundamental. I have a table, say xref, whose columns are primary key values for
other tables. Some of theses keys may be NULL for a given row in xref. I want
to create a query that returns the corresponding entries in the other tables.

xref:
drug_id | function_id|syst_id |text_id
--
d0001   | 2  |   3| 3423
d0001   | 5  || 5678
d0056   | 3  |   5|

system_id:
syst_id | syst_desc
---
3   | renal
4   | hepatic
5   |  respiratory

clinical_text:
text_id| clinical_text
---
3423   | 'some medical mumbo jumbo'
5678   | 'more of the same'

I want the syst_desc and clinical_text (plus other similar data from tables not
shown) given a drug_id and function_id.

Any help?

Thanks a bunch,

Jeff Patterson
The Melanoma Patients' Information Page
http://www.mpip.org



-- 
Jeff Patterson
Site Administrator
The Melanoma Patients' Information Page
http://www.mpip.org/

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [GENERAL] query help

2001-09-07 Thread Stephan Szabo


On Fri, 7 Sep 2001, Jeff Patterson wrote:

 This seems like such a basic function that I'm sure I am missing something
 fundamental. I have a table, say xref, whose columns are primary key values for
 other tables. Some of theses keys may be NULL for a given row in xref. I want
 to create a query that returns the corresponding entries in the other tables.
 
 xref:
 drug_id | function_id|syst_id |text_id
 --
 d0001   | 2  |   3| 3423
 d0001   | 5  || 5678
 d0056   | 3  |   5|
 
 system_id:
 syst_id | syst_desc
 ---
 3   | renal
 4   | hepatic
 5   |  respiratory
 
 clinical_text:
 text_id| clinical_text
 ---
 3423   | 'some medical mumbo jumbo'
 5678   | 'more of the same'
 
 I want the syst_desc and clinical_text (plus other similar data from tables not
 shown) given a drug_id and function_id.

Something like (untested)
select drug_id, function_id, syst_desc, clinical_text from
 (xref left outer join system_id using (syst_id))
  left outer join clinical_text using (text_id);


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] query help

2001-08-31 Thread wsheldah




For one specific label:
select TableB.label from TableA, TableB where TableA.idA=TableB.idA and
TableA.name='test2';

To get a list of name-label pairs:
select TableA.name, TableB.label from TableA, TableB where TableA.idA=TableB.idA
order by TableA.name, TableB.label;





G.L. Grobe [EMAIL PROTECTED] on 08/30/2001 07:45:20 PM

To:   [EMAIL PROTECTED]
cc:(bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  [GENERAL] query help


Hi all,

How would I get TableB.label if all I know about is TableA.name?

I was thinking about a query on TableA first, then within the while
(rs.next()), I'd do another query using the result set from A on B, but I
don't like that much, and it seems kind of slow.

Any help much appreciated!

TableA
idA | name | class
-
11test1   1
12test2   1
13test3   2
14test4   2

TableB
idB | idA | label

1 11 nameA
2 12 nameB
3 13 nameC
4 14 nameD




---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])





---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [GENERAL] Query help

Re: [GENERAL] Query help

[GENERAL] Query help

[GENERAL] Query help

Re: [GENERAL] Query help

Re: [GENERAL] Query help

Re: [GENERAL] Query help

[GENERAL] query help

Re: [GENERAL] query help

Re: [GENERAL] query help

Re: [GENERAL] query help

Re: [GENERAL] query help

Re: [GENERAL] query help

Re: [GENERAL] query help

Re: [GENERAL] query help

Re: [GENERAL] query help

[GENERAL] query help

Re: [GENERAL] query help

Re: [GENERAL] Query help

Re: [GENERAL] Query help

[GENERAL] Query help

Re: [GENERAL] Query help

Re: [GENERAL] Query Help

[GENERAL] Query Help

Re: [GENERAL] Query Help

Re: [GENERAL] query help

[GENERAL] query help

Re: [GENERAL] query help

Re: [GENERAL] query help

29 matches

Site Navigation

Mail list logo

Footer information