Oracle Join Tables


I have the following tbl A and tbl B with sample data.

tbl A
ID     Code     Unit
---      -------     ------
01        A           B

tbl B
FieldName  value
----------------  --------
A                   val_a
B                   val_b

I need the following output

ID     code   code_value     unit   unit_value
01       A         val_a                B       val_b

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you need to join the "lookup" table 2 times:

select A.ID, A.code, l1.value code_value, A.unit, l2.value unit_value
  from tblA a
  LEFT JOIN tableB l1 on l1.FIeldName = A.code  
  LEFT JOIN tableB l2 on l2.FIeldName = A.unit

Open in new window

aliases explained here in case you wonder about the A. and l1 and l2 in the query:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
An alternative that only accesses the tables once BUT has more sorting.

You'll need to do some testing to see which method works best for your actual tables.

select id, 
	min(case when tblA.code=tblB.fieldname then code end) code,
	min(case when tblA.code=tblB.fieldname then value end) code_value,
	min(case when tblA.unit=tblB.fieldname then unit end) unit,
	min(case when tblA.unit=tblB.fieldname then value end) unit_value
from tblA join tblB on tblA.code=tblB.fieldname or tblA.unit=tblB.fieldname
group by id

Open in new window

Mark GeerlingsDatabase AdministratorCommented:
Here is an other query option that works (as Guy Hengel's does) whether there are matching records for both, either or none of the records in table B:

select A.ID, A.code, (select b1.code_value from tbl_B b1 where b1.FieldName = A.code) code_value,
A.unit, (select b2.code_value from tbl_B b2 where b2.FieldName = A.unit) unit_value

I don't know which of these two options will be fastest if your tables have large numbers of records.  I suspect Guy's suggestion may be faster, but I did not test the two.  If you can test both ways in your system, and let us know, that would be helpful.

If you only want the records from tbl_A to be returned if there are matching records in tbl_B for both the code and unit values, then you will need different join syntax (no "left joins" and no nested queries).
Mark GeerlingsDatabase AdministratorCommented:
The comment from slightwv was not here yet when I wrote my previous comment.  His suggestion will return only the rows from tbl_A that have matching records for both values in tbl_b.
slightwv (䄆 Netminder) Commented:
>> will return only the rows from tbl_A that have matching records for both values in tbl_b.

Not "both" since it is an 'or' but there does need to be a match.

If you want ALL records from A even if nothing matches in B then make it a left outer join:
from tblA left outer join tblB on tblA.code=tblB.fieldname or tblA.unit=tblB.fieldname
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.