Needed query for desired output using these 3 tables

Transact-SQL
1

LotAddStock
LotAddStock880×387 62.5 KB

2

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.

1 Like
3

Hi Jeff
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??

Regards
Nandan

4

please see the below link about how to design tables
you can google search
or
you can come up on your own with a much better design

all depends on what you want to do

https://www.sqlshack.com/sql-server-table-structure-overview/

5

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.

6

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.

2 Likes
7

my sql code is like this
SELECT
tblLotAdditionIssueDetails.ItemId,
tblItemMaster.ItemName,
SUM(tblLotAdditionIssueDetails.IssueWt) AS IssuWt,
SUM(tblLotAdditionReceiveDetails.ReceiveWt)
AS ReceiveWt,
tblLotAdditionIssueDetails.IssuePr,
tblLotAdditionIssueDetails.IssueWt - tblLotAdditionReceiveDetails.ReceiveWt AS BalanceWt,
tblLotAdditionIssueDetails.IssueDetailsId
FROM tblLotAdditionIssueDetails
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,
tblItemMaster.ItemName,
tblLotAdditionIssueDetails.IssuePr,
tblLotAdditionIssueDetails.IssueWt - tblLotAdditionReceiveDetails.ReceiveWt,
tblLotAdditionIssueDetails.IssueDetailsId

8

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