Cartesian join

From: Jorge Reyes <jorg_reyes_at_hotmail.com>
Date: Mon, 2 Mar 2009 11:39:45 -0800 (PST)
Message-ID: <cfc0f904-bc69-47db-9092-3e3db6a5c43d_at_r16g2000vbp.googlegroups.com>

Hi everyone,

I Hope can explain well my issue, i have 2 tables, 1 of them has a column which contains a phone number and other column which means the name of a client, the other table has 2 columns which means a range of phone numbers (init range and end range) so i want to get the amount of telephone numbers that do not fit in the range, something like this:

         Table A
Client       PhoneNumber

Nextcom 222222222222
Nextcom 444444444444
Nextcom 111111111111

         Table B
InitRange       EndRange

2222222222 3333333333
5555555555 6666666666

With this i will get an amount of 2 phone numbers are in Table A and are not in any range of Table B, thats what i want... BUT, today i have exactly the inverse, i make this:

SELECT
    COUNT(A.PHONE_NUMBER)
FROM
    om_db.CLIENTS A INNER JOIN om_db.RANGE B ON

    A.PHONE_NUMBER between B.NIR_INICIAL AND B.NIR_FINAL WHERE
    A.CLIENT = 'Nextcom0';

With this i have an amount of 1 phone number, i probe with not between but its not the answer because the result is the product of the cartesian join, Table A has over 1,092 rows for that client (Nextcom0) and the catalog of Table B has 61,704 rows.

Any suggestion please...:( Received on Mon Mar 02 2009 - 13:39:45 CST