Re: [SQL] Check a column value not in Array.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi everybody, I was reading the mails concerning the subject "Check a column value not in Array" and made a quick test. This is the test table: test=# select * from values; id | item - +- 1 | a 2 | b 3 | ab 4 | ac 5 | c 6 | abc (6 Zeilen) Now the first select suggested by Filip: test=# select * from values where item <> ALL ( ARRAY['a', 'b'] ); id | item - +-- 3 | ab 4 | ac 5 | c 6 | abc (4 Zeilen) => why is there 'c'? No the second select suggested by Pavel: test=# select * from values where item = any(array['a','b']); id | item - +-- 1 | a 2 | b (2 Zeilen) => this seems to be correct but I think it is not the result Emi Lu was expecting - or am I wrong? Maybe you were looking for this: test=# select * from values where item <= any(array['a','b']); id | item - +- 1 | a 2 | b 3 | ab 4 | ac 6 | abc (5 Zeilen) Give me all rows, where a or b is in ... Greetz Andy Filip Rembiałkowski schrieb: > 2008/8/14 Emi Lu <[EMAIL PROTECTED]>: >> Greetings, >> >> May I know the command to check whether a column value is in array please? >> >> For example, I am looking for sth like: >> >> select * >> from test >> where test.col not in ARRAY['val1', 'val2']; >> > > select * from test where test.col <> ALL ( ARRAY['val1', 'val2'] ); > > see http://www.postgresql.org/docs/current/static/functions-comparisons.html > > be careful with NULLs in this type of comparisons. > > -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFIpTIhVa7znmSP9AwRAi1/AJ9r5v1TER1JD916+P2EkcyBu4mL+gCgkToT Op4UjxVqH9N20uuzL25QMxU= =bN5x -END PGP SIGNATURE- -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Check a column value not in Array.
select *
from test
where test.col not in ARRAY['val1', 'val2'];
select * from test where test.col <> ALL ( ARRAY['val1', 'val2'] );
see http://www.postgresql.org/docs/current/static/functions-comparisons.html
be careful with NULLs in this type of comparisons.
Thanks a lot for all your inputs!
For efficiency, I guess :
(1) "... where test.col <> all ARRAY['val1', 'val2']"
and
(2) "... where test.col not in ('var1', 'var2')"
provide the same query efficiency right?
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Join question
I have 2 tables, both have a user column. I am currently using a left join from table a to table b because I need to show all users from table a even those not having an entry in table b. The problem is I also have to include items from table b with that have a null user. There are some other criteria as well that are simple where clause filters. So as an example: Table a: Org|user A| emp1 B| emp1 B| emp2 B| emp3 C| emp2 Table b: Org|user|color A |emp1|red A |emp1|blue A |null|pink A |null|orange B |emp1|red B |emp3|red B |null|silver C |emp2|avacado If I: select org, user, count(total) from a left join b on (a.org = b.org and a.user = b.user) where a.org = 'A' group by a.org, a.user order by a.org, a.user I get: Org|user|count A|emp1|2 A|emp2|0 A|emp3|0 But what I need is: A|emp1|2 A|emp2|0 A|emp3|0 A|null|2 Thanks, Edward W. Rouse
Re: [SQL] Join question
Sigh, I messed up the tables a bit when I typed the example, org A was supposed to have entries for all 3 users in table a just like org B does, not just the one. Sorry for the confusion. Edward W. Rouse From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Edward W. Rouse Sent: Friday, August 15, 2008 12:48 PM To: [email protected] Subject: [SQL] Join question I have 2 tables, both have a user column. I am currently using a left join from table a to table b because I need to show all users from table a even those not having an entry in table b. The problem is I also have to include items from table b with that have a null user. There are some other criteria as well that are simple where clause filters. So as an example: Table a: Org|user A| emp1 B| emp1 B| emp2 B| emp3 C| emp2 Table b: Org|user|color A |emp1|red A |emp1|blue A |null|pink A |null|orange B |emp1|red B |emp3|red B |null|silver C |emp2|avacado If I: select org, user, count(total) from a left join b on (a.org = b.org and a.user = b.user) where a.org = 'A' group by a.org, a.user order by a.org, a.user I get: Org|user|count A|emp1|2 A|emp2|0 A|emp3|0 But what I need is: A|emp1|2 A|emp2|0 A|emp3|0 A|null|2 Thanks, Edward W. Rouse
Re: [SQL] Join question
On Fri, Aug 15, 2008 at 9:48 AM, Edward W. Rouse <[EMAIL PROTECTED]> wrote: > The problem is I also have to include > items from table b with that have a null user. There are some other criteria > as well that are simple where clause filters. So as an example: instead of left join try FULL OUTER JOIN. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Join question
I did try that, but I can't get both the values from table a with no entries in table b and the values from table b with null entries to show up. It's either one or the other. Edward W. Rouse -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Broersma Sent: Friday, August 15, 2008 1:10 PM To: Edward W. Rouse Cc: [email protected] Subject: Re: [SQL] Join question On Fri, Aug 15, 2008 at 9:48 AM, Edward W. Rouse <[EMAIL PROTECTED]> wrote: > The problem is I also have to include > items from table b with that have a null user. There are some other criteria > as well that are simple where clause filters. So as an example: instead of left join try FULL OUTER JOIN. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Join question
At 12:20 PM 8/15/2008, [EMAIL PROTECTED] wrote: Date: Fri, 15 Aug 2008 13:46:14 -0400 From: "Edward W. Rouse" <[EMAIL PROTECTED]> To: Subject: Re: Join question Message-ID: <[EMAIL PROTECTED]> I did try that, but I can't get both the values from table a with no entries in table b and the values from table b with null entries to show up. It's either one or the other. Edward W. Rouse Might have luck with applying some additional WHERE clause criteria to your full outer join. So if you don't want certain types NULL's in table b, restrict against that in WHERE clause? I could be misunderstanding the whole thing though.. Steve -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
