Re: Cartesian join
From: Jorge Reyes <jorg_reyes_at_hotmail.com>
Date: Mon, 2 Mar 2009 14:15:41 -0800 (PST)
Message-ID: <de9be03d-95a1-4650-8ac5-e4e9c2bf0ac0_at_l22g2000vba.googlegroups.com>
On 2 mar, 15:51, Ed Prochak <edproc..._at_gmail.com> wrote:
> 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
Date: Mon, 2 Mar 2009 14:15:41 -0800 (PST)
Message-ID: <de9be03d-95a1-4650-8ac5-e4e9c2bf0ac0_at_l22g2000vba.googlegroups.com>
On 2 mar, 15:51, Ed Prochak <edproc..._at_gmail.com> wrote:
> 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
Ok, the logic would be subtract the total amount of records minus all the numbers i don't want, but i make this:
SELECT COUNT(A.PHONE_NUMBER) FROM om_db.CLIENTS A
WHERE NOT EXISTS
(SELECT NIR_INICIAL FROM om_db.RANGE B WHERE A.PHONE_NUMBER between
B.NIR_INICIAL AND B.NIR_FINAL)
AND A.CLIENT = 'Nextcom0';
It seems it works, its that what you mean? Received on Mon Mar 02 2009 - 16:15:41 CST