Fetching record from other table if exist

[Stephen]

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!

[OldPro]

On Oct 24, 11:56 pm, Stephen <[e-mail]:

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 & " ' ;

[Stephen]

On Oct 25, 9:07 pm, OldPro <[e-mail]:

On Oct 24, 11:56 pm, Stephen <[e-mail]:

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.

[OldPro]

On Oct 25, 9:38 am, Stephen <[e-mail]:

On Oct 25, 9:07 pm, OldPro <[e-mail]:

On Oct 24, 11:56 pm, Stephen <[e-mail]:

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"

[Stephen]

On Oct 25, 11:50 pm, OldPro <[e-mail]:

On Oct 25, 9:38 am, Stephen <[e-mail]:

On Oct 25, 9:07 pm, OldPro <[e-mail]:

On Oct 24, 11:56 pm, Stephen <[e-mail]:

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

[Stephen]

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!