SQL Query help

[minestrone]

Would anyone be able to advise on this, im not sure how to do what I want to do.

I have a table CUSTOMER which contains 10,000 rows and has a column MOBILE_NUMBER

I have an OUTGOING_SMS table which contains 3,500 rows and also has a column MOBILE_NUMBER

I'm looking to find all the customers who have not had an SMS which should be 6,500

I know how to find all the customer who have had an SMS, its a simple join but I'm not sure how to find the inverse.

Any help would be fantastic. cheers

DB, is MySQL.

 

[eek]

Would anyone be able to advise on this, im not sure how to do what I want to do.

I have a table CUSTOMER which contains 10,000 rows and has a column MOBILE_NUMBER

I have an OUTGOING_SMS table which contains 3,500 rows and also has a column MOBILE_NUMBER

I'm looking to find all the customers who have not had an SMS which should be 6,500

I know how to find all the customer who have had an SMS, its a simple join but I'm not sure how to find the inverse.

Any help would be fantastic. cheers

DB, is MySQL.

select * from CUSTOMER where customerid not in (select customerid using the query you have)

Its hackville and there will be more optimized versions of it but that will work as a one off...

 

[minestrone]

I think the answer is a NOT IN

 

[minestrone]

select * from CUSTOMER where customerid not in (select customerid using the query you have)

Its hackville and there will be more optimized versions of it but that will work as a one off...

Cheers

 

[minestrone]

Great, that works a treat

 

[mudskipper]

Left join where the join returns null is probably more efficient, but for a one off it matters not. Is it a one off?

 

[computerguru]

Left join where the join returns null is probably more efficient, but for a one off it matters not. Is it a one off?

+1

 

[TheFaQQer]

I think the answer is a NOT IN

NOT EXISTS would be more performant than NOT IN in MySQL.

 

[darrylmg]

NOT EXISTS would be more performant than NOT IN in MySQL.

...and Oracle.
Also, it should perform as good as the NOT IN with the NULL, that was mentioned.
Put an index on the mobile number column in the second table if there's not already one there.

 

[BrilloPad]

...and Oracle.
Also, it should perform as good as the NOT IN with the NULL, that was mentioned.
Put an index on the mobile number column in the second table if there's not already one there.

Assuming its a non-clustered index, you should be careful whether you add indexes or not. For something with alot of inserts/updates and few index uses it might not be worthwhile.