Fetching data from multiple tables applying join using Oracle SQL

Question

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.

@Serg where should i use left join just after the base 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 LEFT join movement m on t.transction_id=m.movement_id where m.name="CASH". is it correct?? — Debo, Sep 23 '21 at 10:20

score 1 · Answer 1 · answered Sep 24 '21 at 07:57

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.

score 0 · Answer 2 · answered Sep 23 '21 at 10:20

0

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'

answered Sep 23 '21 at 10:20

Gordon Linoff

1,242,037
58
646
786

HI @Gordon, if I run alone the query select t.transction_id ,m.movement_id from transction t,movement m where t.transction_id=m.movement_id and m.name="CASH", the record count is 1500. But after applying join it is coming as 3181. Count changed – Debo Sep 23 '21 at 10:30
perhaps you don't need to use `left join` against movements, if for any transaction id you have a movement id. @Gordon solution should work, perhaps your logic is not accurate. – Roberto Hernandez Sep 23 '21 at 10:46
@Roberto hernandez ..didn't get ..doesn't need left join means..how will i connect it then – Debo Sep 23 '21 at 10:58
by normal `join` , like the others, then you can check the counters again – Roberto Hernandez Sep 23 '21 at 11:05
@Debo . . . You would appear to have multiple matches in the `movements` table. – Gordon Linoff Sep 23 '21 at 12:08
@Gordon Linoff, hi when i am executing this query from my java code,it is giving me the exception .ORA01652 :UNABLE TO EXTEND temp segment by 128 in tablespace temporal – Debo Sep 23 '21 at 18:22

Fetching data from multiple tables applying join using Oracle SQL

2 Answers2