SQL query for Finding data with join or where within same table from multi tables-CodePudding

Im sorry i cant explain the question

I have a problem that i need to join 4 tables together to find some information. Im using inner join which is bringing me the right data but duplicate row having a minor change data. Lets say im getting this data

See the Table here

If we look at the results. At the last 4 rows we can notice simillar results. What i want to achieve is these 4 rows converted into 2.

I only want rows whose account id = vehicle account id and 110. I want these two rows. The join query i write is as follows

 select Distinct vh.VehicleNo,jd.AccountID, tr.ID,jd.Memo,je.Description, jd.Cr,jd.Dr ,jd.Detail, je.JEntryId,      je.ExpenseID from tbl_JDetail jd
 inner join tbl_JEntry je on je.JEntryId = jd.JEntryID
 inner join tbl_Trip tr on tr.ID = je.RefID
 inner join tbl_Vehciles vh on vh.VID = tr.VehicleID

I really need to achieve this.. Any help would be appreciated how can i achieve. What i want is let me write the query in simple langauge

The query should first find rows in jdetail with accountid = 110. Then get its jentryid . Then in Jentry it should find the tripid and in trip table i have the vehicle id. Vehicle table contains vehicle id.

Then the query should find in jdetail with same jentryid and vehicleaccountid

i need jdetail accountid = 110 and accountid = vehicle.accountid

The vehicle relation is Jdetail > Jentry > Trip > Vehicle

i hope i was clear enough

CodePudding user response：

Please add supporting information (create table statements, sample data, current output and desired output) to your question. As you have not included supporting information, I am unable to test this. This is my "best guess" based on your description.

select Distinct vh.VehicleNo,jd.AccountID, tr.ID,jd.Memo,je.Description, jd.Cr,jd.Dr ,jd.Detail, je.JEntryId,      je.ExpenseID
from tbl_JDetail jd
inner join tbl_JEntry je
    on je.JEntryId = jd.JEntryID
inner join tbl_Trip tr
    on tr.ID = je.RefID
inner join tbl_Vehciles vh
    on vh.VID = tr.VehicleID
    and vh.AccountID = jd.AccountID
where jd.AccountID = 110

CodePudding user response：

Solved it by using 2 CTEs. CTE1 finds the jentry_id of all records using account ID = 110

and CTE2 find all the required data with inner join cte on cte.JEntryId = jd.JEntryID

It solved all my problems..

Thankyou. Hope it help someone else too