Re: Cartesian join
From: Ed Prochak <edprochak_at_gmail.com>
Date: Mon, 2 Mar 2009 13:51:59 -0800 (PST)
Message-ID: <2c62bdc9-9eff-4007-9b22-1c7f93121518_at_a39g2000yqc.googlegroups.com>
On Mar 2, 2:39 pm, Jorge Reyes <jorg_re..._at_hotmail.com> wrote:
> 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...:(
Date: Mon, 2 Mar 2009 13:51:59 -0800 (PST)
Message-ID: <2c62bdc9-9eff-4007-9b22-1c7f93121518_at_a39g2000yqc.googlegroups.com>
On Mar 2, 2:39 pm, Jorge Reyes <jorg_re..._at_hotmail.com> wrote:
> 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...:(
Consider doing a subquery (in-line view). You have the query which gives you all the numbers you don't want. So think: how can you use that to get the correct answer?
HTH,
ed
Received on Mon Mar 02 2009 - 15:51:59 CST