Selecting data from either of two tables if a key exists

Posted on 2013-09-26
Medium Priority
Last Modified: 2013-09-26
Hi Everyone,

I have a question on how to select data when it exists in either of two tables.

Example:  I have table 'A' that contains the main key (serial number). Table B will have a corresponding record for that key. Table C may have an entry with the matching key.   I would like to using the key from table 'A', extract data from Table 'C' if a matching key exists, otherwise extract data from Table 'B'. NOTE: Table 'B' will always have a matching key/entry, Table 'C' contains only updates for certain keys.

Thanks in advance!
Question by:Mark1M
  • 2
LVL 37

Assisted Solution

momi_sabag earned 400 total points
ID: 39524651
you could do something like this:

with a as (
select 1 as priority, list-of-columns
from C
union all
select 2, list-of-columns
from B
b as (
select *
from A t1 join a t2 on t1.key = t2.key
order by priority
select * from b
fetch first 1 rows only
LVL 18

Accepted Solution

Dave Ford earned 1600 total points
ID: 39525020
Personally, I would use a LEFT OUTER JOIN and a COALESCE to accomplish that. It seems a lot cleaner and easier to me.

 select a.theMainKey,
       coalesce(c.someData, b.someOtherData)
  from TableA a
  left outer join TableB b
    on b.theMainKey = a.theMainKey
  left outer join TableC c
    on c.theMainKey = a.theMainKey

Open in new window


Author Comment

ID: 39525097
Hi momi_sabag,

Thank you for your response and solution.

Author Comment

ID: 39525128
Hi Dave,

Thank you for your response and solution.

Works great!

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
Through the video, you can check the migration process of Outlook PST file to PDF. Kernel for Outlook to PDF tool can convert Outlook emails with all attributes like Subject, To, From, Cc, Bcc and other folders such as Inbox, Outbox, Sent Items, Jun…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question