From: | mike <mike(at)redtux1(dot)uklinux(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Problem with NOT IN and Sub-Select |
Date: | 2005-01-26 15:03:17 |
Message-ID: | 1106751797.9023.60.camel@datacc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have the following query
SELECT vw_mail_add.contact_id, vw_mail_add.first_name,
vw_mail_add.last_name, vw_mail_add.address1, vw_mail_add.add2,
vw_mail_add.add3, vw_mail_add.add4, vw_mail_add.add5, vw_mail_add.add6
FROM vw_mail_add JOIN tb_contact_role ON vw_mail_add.contact_id =
tb_contact_role.contact_id WHERE tb_contact_role.type2 = 72 AND
tb_contact_role.contact_id NOT IN ( SELECT contact_id FROM
tb_contact_role WHERE type2 <> 72);
which gives
contact_id | first_name | last_name | address1 | add2 | add3 | add4 |
add5 | add6
------------+------------+-----------+----------+------+------+------
+------+------
(0 rows)
(aim is to select all records which do not appear in a different
category)
However if I two tables from the queries and do a right join I get
SELECT DISTINCT
tb_client_list1.contact_id,tb_client_list2.contact_id,tb_client_list1.type2 FROM tb_client_list1 RIGHT JOIN tb_client_list2 ON tb_client_list1.contact_id=tb_client_list2.contact_id WHERE tb_client_list1.contact_id IS NULL ORDER BY tb_client_list2.contact_id;
196 rows
Anyone any idea what is going wrong here?
From | Date | Subject | |
---|---|---|---|
Next Message | Danny Lu | 2005-01-26 15:04:35 | how come I get this error message when I try to connect to Postgresql |
Previous Message | Lonni J Friedman | 2005-01-26 14:22:07 | Re: log_min_duration_statement |