Add "NOT NULL" keyword in the WHERE clause of your SUB SELECT statement something like that ..........NOT IN ( SELECT contact_id FROM tb_contact_role WHERE type2 <> 72 AND type2 NOT NULL) On Wed, 26 Jan 2005 15:03:17 +0000, mike <mike@xxxxxxxxxxxxxxxxxxx> wrote: > 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? > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
