Thread: Problem with NOT IN and Sub-Select

Problem with NOT IN and Sub-Select

From

mike

Date:

26 January 2005, 18:03:22

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_list2ON tb_client_list1.contact_id=tb_client_list2.contact_id WHERE tb_client_list1.contact_id IS NULL ORDER
BYtb_client_list2.contact_id; 

196 rows

Anyone any idea what is going wrong here?

Re: Problem with NOT IN and Sub-Select

From

Sibtay Abbas

Date:

26 January 2005, 18:25:08

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@redtux1.uklinux.net> 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_list2ON tb_client_list1.contact_id=tb_client_list2.contact_id WHERE tb_client_list1.contact_id IS NULL ORDER
BYtb_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
>

Re: Problem with NOT IN and Sub-Select

From

Richard Huxton

Date:

26 January 2005, 18:27:54

mike 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);

Don't suppose that subselect could return any NULLs could it?

--
   Richard Huxton
   Archonet Ltd