From: | "Kevin Duffy" <KD(at)wrinvestments(dot)com> |
---|---|
To: | "Andreas Joseph Krogh" <andreak(at)officenet(dot)no>, <pgsql-sql(at)postgresql(dot)org> |
Cc: | "Oliveiros Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt> |
Subject: | Re: simple SQL query |
Date: | 2008-10-29 20:56:14 |
Message-ID: | DFC309C8A42633419600522FA8C4AE1A56106A@mail-01.wrcapital.corp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Gentlemen:
Thanks so much for your assistance.
This returns 512 rows.
select * from tmp_index_member tim
where tim.ISIN NOT IN
(select ISIN from security sec
where ISIN is NOT NULL and
securitytypekey IS NOT NULL and securitytypekey NOT IN ( 5,27) )
Can someone explain why the NULL ISINs in Security is causing
so much grief? I do not get it.
KD
-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Andreas Joseph Krogh
Sent: Wednesday, October 29, 2008 3:58 PM
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] simple SQL query
On Wednesday 29 October 2008 18:39:42 Kevin Duffy wrote:
> Hello:
>
>
>
> I have a couple of queries that are giving me headaches.
>
> They are actually very simple, but I do not understand why
>
> I am not getting the expected results. Maybe I need new glasses.
>
> Please be kind.
>
>
>
> The table definitions are below.
>
>
>
> The table TMP_INDEX_MEMBER contains 21057 rows.
>
> These rows contain 3167 distinct ISINs. ISIN is a type of unique
> security identifier.
>
>
>
> This query
>
> select * from security
>
> where securitytypekey NOT IN ( 5,27) and ISIN IN
>
> (select ISIN from tmp_index_member )
>
> returns 3069 rows. This tells me that there are 3069 ISINs
>
> in the SECURITY table. ISINs that I already know about.
>
>
>
>
>
> update tmp_index_member set securitykey = security.securitykey
>
> from security
>
> where securitytypekey NOT IN (5,27) and tmp_index_member.ISIN =
> security.ISIN
>
> results in Query returned successfully: 20545 rows affected, 2169 ms
> execution time.
>
>
>
> There are now 512 row in TMP_INDEX_MEMBER that have not been updated.
>
> OK now the confusion begins.
>
>
>
> I would expect the following query to return 512 rows. It returns zero.
>
> select * from tmp_index_member tim
>
> where tim.ISIN NOT IN
>
> (select distinct sec.ISIN from security sec where securitytypekey NOT
> IN ( 5,27) )
>
>
>
>
>
> I want to add to SECURITY the securities that are new to me. To do this
> I need the above
>
> query to work.
I bet you have NULLs in some of the rows so your "NOT IN" doesn't work. I suggest you rewrite to something like:
... WHERE (securitytypekey IS NOT NULL OR securitytypekey NOT IN (5,27))...
> Question: does a UNIQUE constraint create an index?
Yes.
> Maybe your fresh eyes will see something obvious.
>
> Many thanks to taking a look at this issue.
--
Andreas Joseph Krogh <andreak(at)officenet(dot)no>
Senior Software Developer / CEO
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment. |
NORWAY | |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Joseph Krogh | 2008-10-29 21:50:16 | Re: simple SQL query |
Previous Message | Andreas Joseph Krogh | 2008-10-29 19:58:27 | Re: simple SQL query |