Fetching data from multiple tables applying join using Oracle SQL-CodePudding

I am new to sql queries. I have base query that fetches data from these 3 tables and below is the query,

select t.transction_id,pt.name,pa.date from transaction t, position_acc pa,position_trans pt where 
t.transction_id=pt.transction_id and pt.postion_id=pa.postition_id

=======================

Now i have a requirement I have to fetch data from another table call, movement

and here is the condition from my client.they need all data from movement table ,even if there is no matching data in position_trans or position_acc.

So, here is the condition my client:

select t.transction_id ,m.movement_id from transction t,movement m where t.transction_id=m.movement_id and m.name="CASH"

How can I club this condition with my base query.client need all data from movement .if matching columns doesn't have entry in position_trans or position_acc,they are fine.They will accept the columns from these column as null.

So, how can I put my movement condition in base query.Please help

======

select t.transction_id ,m.movement_id from transction t,movement m where t.transction_id=m.movement_id and m.name="CASH"

  Record count =1500.
The count should be same after appending with base query.

CodePudding user response：

Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.

In your case, you simply seem to want a LEFT JOIN to movement:

select t.transction_id, pt.name, pa.date, m.movement_id
from position_acc pa join
     position_trans pt
     on pt.postion_id = pa.postition_id join
     transaction t
     on t.transction_id = pt.transction_id left join
     movement m 
     on t.transction_id = m.movement_id and m.name = 'CASH'

CodePudding user response：

This Query returns all entrys that are in transaction and movement, they MUST have a connection otherwise they do not show. Records in position_trans and position_acc are optional in that Query:

select t.transction_id, pt.name, pa.date, m.movement_id 
from transaction t
inner join t.transction_id=m.movement_id
left join position_trans pt on t.transction_id=pt.transction_id
left join position_acc pa on pt.postion_id=pa.postition_id

If you want all records in method no matter if it has a related record in transaction you need to select from method and left join to transaction like this:

select t.transction_id, pt.name, pa.date, m.movement_id 
from method m
left join transaction t on m.movement_id = t.transction_id
left join position_trans pt on t.transction_id=pt.transction_id
left join position_acc pa on pt.postion_id=pa.postition_id

At this point i want to refer to this.