3 Table Join With Spesific Criterias

Other SQL Server Topics
1

Hi,

I have several SQL tables I'm working with, and I want to join each other. I can perform join operations and see the tables under one roof. But there is an error in the join process I did, I need to sort and select the last one at the same time while joining. However, I couldn't do that :frowning: If I had to explain with an example;

I have the following tables;

Table1: StoreStock
Columns: StoreCode, ProductCode, Stock, Time
Rule: This is my main table, we must join other tables here. StoreCode+ProductCode will be used as the key.

Table2: StockOnTheRoad
Columns: TransferNo, StoreCode, ProductCode, OnTheWayStock, Status, Time
Rule: There is more than one status for 1 transfer here. The current status of the transfer is written in the table according to the process. The number of stocks is taken according to the status. "Pending" if the status of the transfer is S1, S2; if the status of the transfer is S3, S4, it is called "OnTheWay". Here, it is only necessary to get the current status number and write it under "Waiting" or "OnTheWay" to sort the statuses and get the current one. So, if a transfer's final status is S4, it will take this number and write it under "OnTheWay", and under "Waiting" if S2. For a product from different transfers, "Waiting" and "OnTheWay" may come together. I use StoreCode+ProductCode as the key.

Table3: StoreOrder
Columns: StoreCode, ProductCode, OpenOrder, Status
Rule: I join the open order quantities for a store in this table into my first table. Here, I remove the statuses "Cancel" and "Done". I use StoreCode+ProductCode as the key.

The most complicated situation here is the correct numbers of Table2 to Table1. I couldn't make it here. Is there anyone who can help? Thank you in advance :slight_smile:

2

Could you post some directly usable sample data and expected join results? That would help clear up the explanation. (That is, CREATE TABLE and INSERT statements for the sample data, rather than just data "splats" on the screen.)

3

I'm sorry for that. But if I knew that much, I would answer this question myself :slight_smile: I can give the attached excel example for the table and query. If you can't help, I understand, I'll try to do something myself :slight_smile:
"we.tl/t-Vngmp1R7jU"

4

But if you can't show which row you would pick to join to from a group of rows, when it's your data, how on earth are we supposed to know which row to join to??

5

In fact, the data will come in SUM according to the specified criteria. In other words, for the StoreCode+ProductCode on the StoreStock table, SUM(OnTheWayStock) will come from the "StockOnTheRoad" table according to the last status, and SUM(OpenOrder) will come from the "StoreOrder" table.

6

We dont have access to your database so f dont provide sample data so that we can emulate your data in ours then hard to help you.

I love gin and tonic by the way

sample data

--DDL

declare @StoreStock table(StoreCode int, ProductCode boolean, 
Stock datetime, Time decimal(10,3)
insert into @StoreStock 
values()

declare @StockOnTheRoad table(TransferNo bit, StoreCode boolean, 
ProductCode int, @float, Status datetime, Time int)
insert into @StockOnTheRoad 
values()
7

I wish so much that I could do what you said and that you guys could help me :frowning: But unfortunately, I haven't mastered that much yet :frowning: The best I can do is the following query. My problem is that I can't add and sum join from the "StockOnTheRoad" table according to status.

select

ss.StoreCode, ss.ProductCode, ss.Stock, ss.Time,

sotr.TransferNo, sotr.StoreCode, sotr.ProductCode, sum(sotr.OnTheWayStock),
case when status in ('S1', 'S2') then 'Pending'
when status in ('S3', 'S4') then 'OnTheWay' else '' end sotr.Status, 
sotr.Time,

so.StoreCode, so.ProductCode, sum(so.OpenOrder), so.Status,

from [dbo].[StoreStock] ss

left join [dbo].[StockOnTheRoad] sotr on sotr.(StoreCode+ProductCode)=ss.(StoreCode+ProductCode)
left join [dbo].[StoreOrder] so on so.(StoreCode+ProductCode)=ss.(StoreCode+ProductCode)

where so.Status not in ('Cancel', 'Done')
8

We all start mastering something by starting to do it.

Why cant you do whatvwqs asked of you?

9

Because I'm not skilled enough like you to know exactly how to do it :frowning: Thanks for your help :slight_smile:

10

Given your description here is a guess at the required test rig.

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON
GO
CREATE TABLE #StoreStock
(
	StoreCode char(2) NOT NULL
	,ProductCode varchar(7) NOT NULL
	,Stock int NOT NULL
	,[Time] datetime NOT NULL
	,PRIMARY KEY (StoreCode, ProductCode)
);
INSERT INTO #StoreStock
VALUES ('AA', 'PP1', 10, '20220712')
	,('AA', 'PP2', 20, '20220712')
	,('BB', 'PP1', 5, '20220710')
	,('BB', 'PP2', 23, '20220710');
CREATE TABLE #StockOnTheRoad
(
	TransferNo int NOT NULL PRIMARY KEY
	,StoreCode char(2) NOT NULL
	,ProductCode varchar(7) NOT NULL
	,OnTheWayStock int NOT NULL
	,[Status] char(2) NOT NULL -- S1, S2 = 'Pending'; S3, S4 = OnTheWay
	,[Time] datetime NOT NULL
);
INSERT INTO #StockOnTheRoad
VALUES (1, 'AA','PP1', 3, 'S3', '20220714')
	,(2, 'AA','PP1', 2, 'S4', '20220714')
	,(3, 'AA','PP1', 1, 'S1', '20220714');
CREATE TABLE #StoreOrder
(
	StoreCode char(2) NOT NULL
	,ProductCode varchar(7) NOT NULL
	,OpenOrder int NOT NULL
	,[Status]  varchar(10) NOT NULL -- ignore Cancel and Done
);
INSERT INTO #StoreOrder
VALUES ('AA','PP1', 3 ,'NotDone')
	,('AA','PP1', 2 ,'NotDone');

We really need the correct datatypes with sensible test data and the expected results for the given test data.
Here is an even bigger guess at the sort of query required:

SELECT ss.StoreCode, ss.ProductCode, ss.Stock, ss.Time
	,sotr.TransferNo, sotr.StoreCode, sotr.ProductCode
	,SUM(sotr.OnTheWayStock) OVER (PARTITION BY sotr.StoreCode, sotr.ProductCode) AS SumOnTheWayStock
	,CASE
		WHEN sotr.[status] IN ('S1', 'S2')
		THEN 'Pending'
		WHEN sotr.[status] IN ('S3', 'S4')
		THEN 'OnTheWay'
		ELSE ''
	END AS sotr_status
	,sotr.[Time]
	,so.StoreCode, so.ProductCode
	,SUM(so.OpenOrder) OVER (PARTITION BY so.StoreCode, so.ProductCode) AS OpenOrder
	,so.[Status] AS so_status
FROM #StoreStock ss
	LEFT JOIN #StockOnTheRoad sotr
		ON ss.StoreCode = sotr.StoreCode
			AND ss.ProductCode = sotr.ProductCode
	LEFT JOIN #StoreOrder so
		ON ss.StoreCode = so.StoreCode
			AND ss.ProductCode = so.ProductCode
			AND NOT so.[Status] IN ('Cancel', 'Done');
-- Putting this in the where clause will convert the left join to an inner join
-- WHERE NOT so.[Status] IN ('Cancel', 'Done');

If you can create a better test rig with the expected results for the data in the test rig someone should be able to give you a better answer.
We cannot see your system and our telepathic powers are limited!

1 Like