0

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.
Improve this question
2
  • Use standard JOIN syntax. You want LEFT JOIN. Commented Sep 23, 2021 at 10:18
  • @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?? Commented Sep 23, 2021 at 10:20

2 Answers 2

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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'
Improve this answer

6 Comments

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
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 ..didn't get ..doesn't need left join means..how will i connect it then
by normal join , like the others, then you can check the counters again
@Debo . . . You would appear to have multiple matches in the movements table.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.