I have the following query that isn't behaving like I would expect:
select * from strains s where s.id in (select strain_id from pathway_strains);
I would expect each strain record to appear only once. Instead I get output
like this, where the same strain id appears many times:
id | name | organism
-------+--------------+---------- 83 | common | 82 83 | common | 82 83 | common |
82 83 | common | 82 83 | common | 82 83 | common | 82 83 | common
| 82 83 | common | 82 83 | common | 82 83 | common | 82 506 |
common | 487 506 | common | 487
... continues
By the way, this output is the same as if running the query:
select * from strains s join pathway_strains ps on ps.strain_id = s.id;
=====================================
Table "public.strains" Column | Type | Modifiers
---------------+--------------+---------------------------------------------------------id | integer |
notnull name | text | not null default 'common'::textorganism | integer | not null
Indexes: "strains_pkey" PRIMARY KEY, btree (id)
================================== View "public.pathway_strains" Column | Type | Modifiers
------------+---------+-----------pathway_id | integer |strain_id | integer |
View definition:SELECT DISTINCT p.id AS pathway_id, c.strain_id FROM catalyst_associations c JOIN pathway_edges e ON
c.pathway_edge_id= e.id RIGHT JOIN pathways p ON p.id = e.pathway_id ORDER BY p.id, c.strain_id;
The contents of pathways_strains are likepathway_id | strain_id
------------+----------- 2083 | 76 2083 | 80 2083 | 83 2083 | 95
2084| 76 2084 | 80 2084 | 83 2084 | 95 2084 | 162
...etc
So, am I wrong in expecting each strain record to appear only once in the
result set? Or is there something wrong with PostgreSQL? I would be happy
to provide more information if it's needed.
Thank you!
Luca
ps: # select version(); version
-----------------------------------------------------------------------------------------------------------------PostgreSQL
8.0.3on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-42)