How to get oracle inner join query result in pandas

Using Streamlit
,
1

Dear Sir

Please review all my dataframes and queries. I require an inner join of three dataframes similar to the Oracle SQL query sample provided below. How can I achieve this type of join in pandas? I would appreciate your guidance.

Import pandas as pd

Employees (Parent table)

employees = pd.DataFrame({
‘emp_id’: [1, 2, 3],
‘name’: [‘Alice’, ‘Bob’, ‘Charlie’]
})

Departments (Child table - FK is emp_id)

departments = pd.DataFrame({
‘dept_id’: [101, 102, 103],
‘emp_id’: [2, 3, 4], # 4 doesn’t exist in employees
‘item_id’: [2, 3, 4], # 4 doesn’t exist in employees
‘dept_name’: [‘HR’, ‘IT’, ‘Finance’]
})

itemtab = pd.DataFrame({
‘dept_id’: [101, 102, 103,‘’,‘’,‘’],
‘item_id’: [1,2, 3, 4,5,6], # 4 doesn’t exist in employees
‘item_name’: [‘computer’, ‘car’, ‘bus’,‘home’, ‘flate’, ‘treain’]
})

SQL equivalent:

need this oracle this type of query result SELECT * FROM employees, departments, itemtab where employees.emp_id = departments.emp_id and departments.item_id = itemtab.item_id

i try this code in python but it give me error

result = pd.merge(employees, departments, itemtab,left_on=[‘emp_id’,‘item_id’], right_on=[‘emp_id’,‘item_id’], how=‘inner’)

print(result)

2

Welcome to the community and thanks for your detailed question! :blush: It could be possible that the error is due to the way you’re merging multiple DataFrames in pandas. In pandas, you can’t merge more than two DataFrames at once using a single pd.merge() call. Instead, you should perform the joins step-by-step, similar to chaining SQL joins. According to this discussion, you should first merge employees and departments on emp_id, then merge the result with itemtab on item_id.

Here’s a concise example based on your data:

import pandas as pd

# Your DataFrames
employees = pd.DataFrame({
    'emp_id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie']
})

departments = pd.DataFrame({
    'dept_id': [101, 102, 103],
    'emp_id': [2, 3, 4],
    'item_id': [2, 3, 4],
    'dept_name': ['HR', 'IT', 'Finance']
})

itemtab = pd.DataFrame({
    'dept_id': [101, 102, 103, '', '', ''],
    'item_id': [1, 2, 3, 4, 5, 6],
    'item_name': ['computer', 'car', 'bus', 'home', 'flate', 'treain']
})

# Stepwise inner joins
result = pd.merge(employees, departments, on='emp_id', how='inner')
result = pd.merge(result, itemtab, on='item_id', how='inner')
print(result)

This approach should give you the equivalent of your Oracle SQL inner join. If you want more detail or a breakdown of each step, just let me know!

Sources:

3

but sir

i want result in one code not step by step

result = pd.merge(employees, departments, itemtab,left_on=[‘emp_id’,‘item_id’], right_on=[‘emp_id’,‘item_id’], how=‘inner’)

not join two df want to join all df one time with pk and fk like as oracle query