Fetching record from other table if exist

Hello,

I have 2 tables, one contains log of all incoming phone calls, and the
other one is simply a phone book.
I want to fetch caller's name if it's already exist in the phone book,
and if it doesn't exist yet just fetch caller's phone number.
Is it possible to do this in just a single query?
Something like:

SELECT Switch((SELECT count(a.*) FROM phone_log a, phone_book b WHERE
a.phone_number = b.phone_number) <> 0, b.member_name, (SELECT
count(a.*) FROM phone_log a, phone_book b WHERE a.phone_number =
b.phone_number) = 0, a.phone_number) FROM phone_log a, phone_book b

Thanks!

Re: Fetching record from other table if exist

On Oct 24, 11:56 pm, Stephen wrote:
> Hello,
>
> I have 2 tables, one contains log of all incoming phone calls, and the
> other one is simply a phone book.
> I want to fetch caller's name if it's already exist in the phone book,
> and if it doesn't exist yet just fetch caller's phone number.
> Is it possible to do this in just a single query?
> Something like:
>
> SELECT Switch((SELECT count(a.*) FROM phone_log a, phone_book b WHERE
> a.phone_number = b.phone_number) <> 0, b.member_name, (SELECT
> count(a.*) FROM phone_log a, phone_book b WHERE a.phone_number =
> b.phone_number) = 0, a.phone_number) FROM phone_log a, phone_book b
>
> Thanks!

It sounds like you need a LEFT JOIN.
sSQL="SELECT a.phone_number, b.member_name FROM a LEFT JOIN b ON
a.phone_number=b.phone_number WHERE a.phone_number = ' " &
sPhoneNumber & " ' ;

Re: Fetching record from other table if exist

On Oct 25, 9:07 pm, OldPro wrote:
> On Oct 24, 11:56 pm, Stephen wrote:
>
> > Hello,
>
> > I have 2 tables, one contains log of all incoming phone calls, and the
> > other one is simply a phone book.
> > I want to fetch caller's name if it's already exist in the phone book,
> > and if it doesn't exist yet just fetch caller's phone number.
> > Is it possible to do this in just a single query?
> > Something like:
>
> > SELECT Switch((SELECT count(a.*) FROM phone_log a, phone_book b WHERE
> > a.phone_number = b.phone_number) <> 0, b.member_name, (SELECT
> > count(a.*) FROM phone_log a, phone_book b WHERE a.phone_number =
> > b.phone_number) = 0, a.phone_number) FROM phone_log a, phone_book b
>
> > Thanks!
>
> It sounds like you need a LEFT JOIN.
> sSQL="SELECT a.phone_number, b.member_name FROM a LEFT JOIN b ON
> a.phone_number=b.phone_number WHERE a.phone_number = ' " &
> sPhoneNumber & " ' ;

Hello,
Thanks for the help.

I'm afraid that's not the solution to my problem, because that would
mean i have to pass variable to the query.
While what i really want is simply making a check from table phone
book.
If there's already a record in that table that contains caller's phone
number use member name, otherwise use the
phone number.

Re: Fetching record from other table if exist

On Oct 25, 9:38 am, Stephen wrote:
> On Oct 25, 9:07 pm, OldPro wrote:
>
>
>
>
>
> > On Oct 24, 11:56 pm, Stephen wrote:
>
> > > Hello,
>
> > > I have 2 tables, one contains log of all incoming phone calls, and the
> > > other one is simply a phone book.
> > > I want to fetch caller's name if it's already exist in the phone book,
> > > and if it doesn't exist yet just fetch caller's phone number.
> > > Is it possible to do this in just a single query?
> > > Something like:
>
> > > SELECT Switch((SELECT count(a.*) FROM phone_log a, phone_book b WHERE
> > > a.phone_number = b.phone_number) <> 0, b.member_name, (SELECT
> > > count(a.*) FROM phone_log a, phone_book b WHERE a.phone_number =
> > > b.phone_number) = 0, a.phone_number) FROM phone_log a, phone_book b
>
> > > Thanks!
>
> > It sounds like you need a LEFT JOIN.
> > sSQL="SELECT a.phone_number, b.member_name FROM a LEFT JOIN b ON
> > a.phone_number=b.phone_number WHERE a.phone_number = ' " &
> > sPhoneNumber & " ' ;
>
> Hello,
> Thanks for the help.
>
> I'm afraid that's not the solution to my problem, because that would
> mean i have to pass variable to the query.
> While what i really want is simply making a check from table phone
> book.
> If there's already a record in that table that contains caller's phone
> number use member name, otherwise use the
> phone number.- Hide quoted text -
>
> - Show quoted text -

How do you know the caller's phone number without a variable? If you
are just looking for a report of all callers and their matching names,
then modify the query slightly:
sSQL="SELECT a.phone_number, b.member_name FROM a LEFT JOIN b ON
a.phone_number=b.phone_number"

Re: Fetching record from other table if exist

On Oct 25, 11:50 pm, OldPro wrote:
> On Oct 25, 9:38 am, Stephen wrote:
>
>
>
> > On Oct 25, 9:07 pm, OldPro wrote:
>
> > > On Oct 24, 11:56 pm, Stephen wrote:
>
> > > > Hello,
>
> > > > I have 2 tables, one contains log of all incoming phone calls, and the
> > > > other one is simply a phone book.
> > > > I want to fetch caller's name if it's already exist in the phone book,
> > > > and if it doesn't exist yet just fetch caller's phone number.
> > > > Is it possible to do this in just a single query?
> > > > Something like:
>
> > > > SELECT Switch((SELECT count(a.*) FROM phone_log a, phone_book b WHERE
> > > > a.phone_number = b.phone_number) <> 0, b.member_name, (SELECT
> > > > count(a.*) FROM phone_log a, phone_book b WHERE a.phone_number =
> > > > b.phone_number) = 0, a.phone_number) FROM phone_log a, phone_book b
>
> > > > Thanks!
>
> > > It sounds like you need a LEFT JOIN.
> > > sSQL="SELECT a.phone_number, b.member_name FROM a LEFT JOIN b ON
> > > a.phone_number=b.phone_number WHERE a.phone_number = ' " &
> > > sPhoneNumber & " ' ;
>
> > Hello,
> > Thanks for the help.
>
> > I'm afraid that's not the solution to my problem, because that would
> > mean i have to pass variable to the query.
> > While what i really want is simply making a check from table phone
> > book.
> > If there's already a record in that table that contains caller's phone
> > number use member name, otherwise use the
> > phone number.- Hide quoted text -
>
> > - Show quoted text -
>
> How do you know the caller's phone number without a variable? If you
> are just looking for a report of all callers and their matching names,
> then modify the query slightly:
> sSQL="SELECT a.phone_number, b.member_name FROM a LEFT JOIN b ON
> a.phone_number=b.phone_number"

Hello,

I get the caller's phone number from the phone_log table.
Yes, it's like matching names, but when i found no record (caller's
phone number) on phone book table simply return caller's phone number
in the phone_log table.
With your query, i believe would return NULL record if there's no
matching phone number and that's not what i want.

Thanks

Re: Fetching record from other table if exist

Hello,

I modified your last query, and it works!
Here's the working query:

SELECT IIF(b.member_name IS NULL, a.phone_number, b.member_name) FROM
phone_log a LEFT JOIN phone_book b ON a.phone_number = b.phone_number

Thanks for your help, OldPro!