Simple. Do a LEFT JOIN to the tblItemMaster table from the other two tables based on the obvious column that will join the 3 tables.
Give it a try.
thank u very much. I have tried your solution its work. But some people advice me to do union and do it. I tried both way but i think your solution is fast and optimized. Any suggestion on table structure??
please see the below link about how to design tables
you can google search
you can come up on your own with a much better design
all depends on what you want to do
You'd need to post the "UNION Structure" code that you're talking about. I don't see a way to pull off the output you said you wanted using UNION. Have the people that are saying to do it that way give you code that does it that way.
The only reason I didn't post code is because you didn't post any "Readily Consumable" data in the form of INSERT/VALUES into table that include the create statements.
In the table structure thing, I see no reason for having both the ReceiptID and the ReceiptDetailsID. As for the other IDs, I have no idea because I don't know about the other tables cited just like I don't know about the Receipt tables.
I will say that "tbl-ing" table names isn't the right thing to do. I've seen too many times where a table needed to become a view with an INSTEAD OF trigger or similar and, in order to keep from having to change code everywhere, you now have a view with a "tbl" prefix on it. So far as I'm concerned, "tbl-ing" is not nor has ever been a "Best Practice". A million people did it wrong but, because of the widespread usage, it earned the title by mistake.
my sql code is like this
SUM(tblLotAdditionIssueDetails.IssueWt) AS IssuWt,
tblLotAdditionIssueDetails.IssueWt - tblLotAdditionReceiveDetails.ReceiveWt AS BalanceWt,
INNER JOIN tblItemMaster
ON tblLotAdditionIssueDetails.ItemId = tblItemMaster.ItemId
LEFT OUTER JOIN tblLotAdditionReceiveDetails
ON tblLotAdditionIssueDetails.ItemId = tblLotAdditionReceiveDetails.ItemId
AND tblLotAdditionIssueDetails.IssueDetailsId = tblLotAdditionReceiveDetails.IssueDetailsId
GROUP BY tblLotAdditionIssueDetails.ItemId,
tblLotAdditionIssueDetails.IssueWt - tblLotAdditionReceiveDetails.ReceiveWt,
your SQL Code formatted .. makes it easier to read ..i made tbl into tbl_
SELECT tbl_lotadditionissuedetails.itemid, tbl_itemmaster.itemname, Sum(tbl_lotadditionissuedetails.issuewt) AS IssuWt, Sum(tbl_lotadditionreceivedetails.receivewt) AS ReceiveWt, tbl_lotadditionissuedetails.issuepr, tbl_lotadditionissuedetails.issuewt - , tbl_lotadditionreceivedetails.receivewt AS BalanceWt, tbl_lotadditionissuedetails.issuedetailsid FROM tbl_lotadditionissuedetails INNER JOIN tbl_itemmaster ON tbl_lotadditionissuedetails.itemid = tbl_itemmaster.itemid LEFT OUTER JOIN tbl_lotadditionreceivedetails ON tbl_lotadditionissuedetails.itemid = tbl_lotadditionreceivedetails.itemid AND tbl_lotadditionissuedetails.issuedetailsid = tbl_lotadditionreceivedetails.issuedetailsid GROUP BY tbl_lotadditionissuedetails.itemid, tbl_itemmaster.itemname, tbl_lotadditionissuedetails.issuepr, tbl_lotadditionissuedetails.issuewt - , tbl_lotadditionreceivedetails.receivewt