CODEWITHSUNDEEP
sqloracle-database
mrdiu
mrdiu

Reputation: 49

How do I join two tables with mismatching values that are the same? Oracle SQL

Not quite sure how to explain this, but it's a basic query I'm struggling with. Essentially under the same column under two different tables, there's two values that are different from each other but are the same, and I'm unable to get any results due to the values being different.

Any help would be greatly appreciated, thank you in advance.

Existing Query

select a.company, a.customer, b.document
from table1 a left join table2 b
on a.company=b.company and a.customer=b.customer
where a.company='M1'

Example.

table1
Company | Customer |
M1         10000

table2
Company | Customer | Document
01         10000      811

Result I'm trying to get

Company | Customer | Document
M1         10000       811

Upvotes: 0

Views: 41

Answers (2)

Popeye
Popeye

Reputation: 35900

If you need to use the company also then I can see the logic is M and company code will match with other tables company code.

You should try this:

select a.company, a.customer, b.document
from table1 a left join table2 b
on a.company= 'M' || to_number(b.company) 
and a.customer=b.customer
where a.company='M1'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

It looks like you want a join only on the customer column:

select a.company, a.customer, b.document
from table1 a left join
     table2 b
     on a.customer = b.customer
where a.company = 'M1';

If you need to take the company into account, then you can filter on both values:

select a.company, a.customer, b.document
from table1 a left join
     table2 b
     on a.customer = b.customer and b.company = '01'
where a.company = 'M1';

Upvotes: 1

Related Questions