LEFT JOIN IN MULTIPLE VIEW - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

LEFT JOIN IN MULTIPLE VIEW

Author

Topic

ryandavebrigino@yahoo.com
Starting Member

9 Posts

Posted - 2008-07-17 : 01:10:46

I am creating a T-SQL that will retrieve records from multiple views; the views are made of 5 identical tables that hold transaction data from different store locations,
My logic is to create a query that will retrieve the value from the view and join the value from other views filtered by DATE. And sadly, it doesn’t work fine.

My target result is this:

[Store] [TransferIN] [OnTransit] [TransferOUT] [OnTransit]
Store1 1000 500 200 200
Store2 5000 0 1400 800
Store3 2000 800 12000 7000
Store4 0 900 200 400
Store5 1900 0 0 0

But my query doesn’t return the rows that contain null values. In most cases I use the LEFT JOINT keyword to include non existing values in the relation. But here, it doesn’t works as expected.

When i use the LEFT JOIN keyword, the result is like this

[Store] [TransferIN] [OnTransit] [TransferOUT] [OnTransit]
Store1 1000 500 200 200
Store3 2000 800 12000 7000

Seems like using INNER JOIN

my SP T-SQL is this

ALTER PROCEDURE [dbo].[dalspRP_EXECUTIVE_GetTransactionTotalAllStore]
@Date as varchar(20)
AS
BEGIN
SET NOCOUNT ON;

SELECT
Tbl_SALES_LocationMap.s_LocationSPReference,
SUM(View_Transfer_AllStore.dec_ItemTrnsfrTotal) AS Transfer,
SUM(dbo.View_Transfer_AllStoreOnTransit.dec_ItemTrnsfrTotal) AS TransferOnTransit
FROM
Tbl_SALES_LocationMap
left JOIN View_Transfer_AllStore
ON View_Transfer_AllStore.s_LocationSPReference = Tbl_SALES_LocationMap.s_LocationSPReference
Left JOIN dbo.View_Transfer_AllStoreOnTransit
ON dbo.View_Transfer_AllStoreOnTransit.s_LocationSPReference = Tbl_SALES_LocationMap.s_LocationSPReference
where
Tbl_SALES_LocationMap.[b_LocationIsActive] = 'True'
AND Tbl_SALES_LocationMap.[s_LocationSPReference] <> 'Warehouse'

AND (MONTH(View_Transfer_AllStore.dt_ItemTrnsfrFromDate) = MONTH(@Date))
AND (YEAR(View_Transfer_AllStore.dt_ItemTrnsfrFromDate) = YEAR(@Date))

AND (MONTH(View_Transfer_AllStoreOnTransit.dt_ItemTrnsfrFromDate) = MONTH(@Date))
AND (YEAR(View_Transfer_AllStoreOnTransit.dt_ItemTrnsfrFromDate) = YEAR(@Date))

GROUP BY Tbl_SALES_LocationMap.s_LocationSPReference

Thanks...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 01:26:44

modify and conditions like this

AND ((MONTH(View_Transfer_AllStore.dt_ItemTrnsfrFromDate) = MONTH(@Date)) 
AND (YEAR(View_Transfer_AllStore.dt_ItemTrnsfrFromDate) = YEAR(@Date))
OR View_Transfer_AllStore.dt_ItemTrnsfrFromDate IS NULL)
AND ((MONTH(View_Transfer_AllStoreOnTransit.dt_ItemTrnsfrFromDate) = MONTH(@Date)) 
AND (YEAR(View_Transfer_AllStoreOnTransit.dt_ItemTrnsfrFromDate) = YEAR(@Date)) OR View_Transfer_AllStoreOnTransit.dt_ItemTrnsfrFromDate IS NULL)

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-07-17 : 01:26:58

You need something like a 'Store' table which has all the stores in it & then left join to all the other tables

ryandavebrigino@yahoo.com
Starting Member

9 Posts

Posted - 2008-07-17 : 02:34:15

Regarding the reply of visakh16
the it works for some dates but not for all dates i update the query and it returns this

StaMariaMain 12554142.00 10103178.96 73682311.50 46914823.20
Angeles 906541.02 NULL 8006580.14 3726723.00
Balagtas NULL NULL NULL NULL
StaMariaAnnex 674913.75 NULL 5230892.50 4209657.90

which is fine but for the previous month it only returns this

Balagtas NULL NULL NULL NULL

and to dexter.knudson
I already have a StoreTable in the query its name is 'Tbl_SALES_LocationMap'
but still doesnt work even using Left Joins

I am trying a new code using CASE keyword but i have systax error
can somebody please help me if you know where the errors are in my code.

this is the new code:
---==================================================

ALTER PROCEDURE [dbo].[dalspRP_EXECUTIVE_GetTransactionTotalAllStore]
@Date as varchar(20)
AS
BEGIN
SET NOCOUNT ON;

SELECT
Tbl_SALES_LocationMap.s_LocationSPReference,

Transfer = CASE SUM(View_Transfer_AllStore.dec_ItemTrnsfrTotal) WHEN SUM(View_Transfer_AllStore.dec_ItemTrnsfrTotal) NOT NULL THEN (SUM(View_Transfer_AllStore.dec_ItemTrnsfrTotal)) else 'Unknown' end

FROM
Tbl_SALES_LocationMap
left JOIN View_Transfer_AllStore
ON View_Transfer_AllStore.s_LocationSPReference = Tbl_SALES_LocationMap.s_LocationSPReference

where
Tbl_SALES_LocationMap.[b_LocationIsActive] = 'True'
AND Tbl_SALES_LocationMap.[s_LocationSPReference] <> 'Warehouse'

AND (MONTH(View_Transfer_AllStore.dt_ItemTrnsfrFromDate) = MONTH(@Date))
AND (YEAR(View_Transfer_AllStore.dt_ItemTrnsfrFromDate) = YEAR(@Date))

GROUP BY Tbl_SALES_LocationMap.s_LocationSPReference
end

---================================================
the error message is this.

Msg 156, Level 15, State 1, Procedure dalspRP_EXECUTIVE_GetTransactionTotalAllStore, Line 17
Incorrect syntax near the keyword 'NOT'.

but im just trying to follow this example

SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titles

thanks in advance for help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 02:48:56

can you provide some data sample and explain what output you want out of them?

ryandavebrigino@yahoo.com
Starting Member

9 Posts

Posted - 2008-07-17 : 03:34:55

These are the Name of the Store from 'Tbl_SALES_LocationMap'
Angeles
Baliuag Main
Malolos
Sta. Maria Annex
Sta. Maria Main

This are the records from Transaction Tables

Date AmountTotal StoreLocation
2006-07-21 00:00:00 1876.80 Angeles
2007-07-21 00:00:00 21150.00 Angeles
2006-07-11 00:00:00 1410.00 BaliuagMain
2007-07-16 00:00:00 2250.00 BaliuagMain
2006-07-13 00:00:00 17263.20 Malolos
2006-07-16 00:00:00 31500.00 Malolos
2006-07-16 00:00:00 4410.00 Malolos
2007-07-01 00:00:00 1450.00 StaMariaAnnex
2007-07-12 00:00:00 2850.00 StaMariaAnnex
2007-07-21 00:00:00 1539.00 StaMariaAnnex
2007-07-14 00:00:00 3780.00 StaMariaMain
2007-07-19 00:00:00 2650.00 StaMariaMain

Each Store have its own Transaction Tables from the database, the transactionTables are Tbl_TransferToReceive, Tbl_TransferOUT, Tbl_PurchaseToReceive, and Tbl_Return.
Each tables have the column StoreLocationName and TotalAmount

My logic is to consolidate each table from each store into one view, and I came up having
View_TransferToReceive, View_TransferOUT, View_PurchaseToReceive, and View_Return.

AND Then in my T-SQL
I planned on Joining all the tables by StoreLocationName as the relationship

Then retrieve the SUM of TotalAmount column and grouping them by StoreLocationName

My target result is like this

[Store] [TransferToReceive] TransferOUT] [PurchaseToReceive] [Return]

Angeles 100 100 100 200
BaliuagMain 0 0 100 20
Malolos 12 3 40 590
StamariaAnnex 9 300 0 0
StaMariaMain 0 0 89 0

Thanks… visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 04:04:55

[code]SELECT loc.StoreLocation,ISNULL(tmp.Total,0)
FROM Tbl_SALES_LocationMap loc
LEFT JOIN (
SELECT StoreLocation,SUM(AmountTotal) AS Total
FROM(
SELECT AmountTotal, StoreLocation
FROM View_TransferToReceive
UNION ALL
SELECT AmountTotal, StoreLocation
FROM View_TransferOUT
UNION ALL
SELECT AmountTotal, StoreLocation
FROM View_PurchaseToReceive
UNION ALL
SELECT AmountTotal, StoreLocation
FROM View_Return)trsn
GROUP BY StoreLocation
)tmp
ON tmp.StoreLocation =loc.StoreLocation [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources