Sql join more than two tables-CodePudding

I have three tables transaction, transaction-docs and generic-transaction-task. transaction-docs and generic-transaction-task tables are related on transaction table on parameter transactionId (foreign key relation).I want to join these three tables and fetch data on the basis of transactionid.

I want data from all these three tables on the basis of transactionid.

If I am trying with below query then getting repeated records.

select A.Id , B.Id , c.Id 
from  [velvet_elves].[dbo].[Transactions] as A
Left  join [dbo].[TransactionsDocs] as C On A.Id = C.TransactionId
Left  join [dbo].[GenericTransactionTask] as B on A.Id = B.TransactionId
Where A.Id in (24147, 24149)

CodePudding user response：

If I am trying with below query then getting repeated records.

This sounds like the query is doing exactly what it is meant to do, it's just that you're either not understanding either what a JOIN does, or you're not understanding the relationship of your data.

What is happening is that there are multiple records on one (or more) of the joined tables which satisfy the criteria that you have given. What happens then is that each matching record will be placed in the results, joined to each matching record. So if there are multiple matching records, you will get the same Id appearing multiple times.

Without knowing your tables or your data, I can't be more specific.

CodePudding user response：

then getting repeated records.

You shouldn't, if these Id columns are properly unique

If you have:

Transactions
1, Tran1

TransactionDocs
Id, TransactionId, Name
1, 1, TranDoc1
2, 1, TranDoc2

GenericTranTasks
Id, TransactionId, Name
1, 1, GenericTranTask1
2, 1, GenericTranTask2

When you join then all you get:

TransId, TranName, DocId, DocTranId, DocName, TaskId, TaskTranId, TaskName 
1, Tran1, 1, 1, TranDoc1, 1, 1, GenericTranTask1
1, Tran1, 2, 1, TranDoc2, 1, 1, GenericTranTask1
1, Tran1, 1, 1, TranDoc1, 2, 1, GenericTranTask2
1, Tran1, 2, 1, TranDoc2, 2, 1, GenericTranTask2
^         ^               ^

(You're selecting only those columns with ^ underneath)

Every one of these things is unique, but you can't look at the data in a single column and say "the tran id is repeated 4 times!" - you have to look at the total record, and these are all unique when you consider all 3 numbers together:

1, 1, 1
1, 2, 1
1, 1, 2
1, 2, 2

This is essentially just the flat rendering of a tree of data that looks like:

..and it's what databases do when you ask them to join data; they output a rectangular block, with individal input records repeated where necessary

Every time you add another join, you grow the tree; you add more leaves to everything that is currently a leaf. From what you said in the comments it seems like what you wctually need is two separate trees.

In summary, you're joining together data that isn't related at all: a tran task and a tran doc are not related in any way, just like a person's job history and their amazon order history are not related - just because I've had 6 jobs in my life, and i've ordered 20 things from amazon in my life; doesn't mean I should join my jobs with my orders (120 records result). Now, you could make a relationship, by reframing the question as "what jobs have you had, and while you were working in each of those jobs, what did you order from amazon" - in which case we insist that the order.order_date be between the job.hire_date and the job.fire_date, so that takes this exploding relationship where every job is crossed with every order, and limits it so that jobs and order are related based on the date each occurred - but you have to ask the right question ..

I suspect that you may not be asking the right question of your data..