Joining 4 Different Tables - 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

General SQL Server Forums

New to SQL Server Programming

Joining 4 Different Tables

Author

Topic

jasluv
Starting Member

8 Posts

Posted - 2012-08-02 : 08:31:56

Hi guys, Can you help me how do you query those table above. so that the results will only display the data in ECOTransactions table regardless if all the relationship has null value.

I can do it easily without the FillWeight table by just using the LEFT OUTER JOIN on table Packstyle and Ordertype, now my problem is if I include the FillWeight table.

I really couldn't figure out how to do it because the FillWeight has relationships on those two tables and the packstyle column has null value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-02 : 09:40:11

it should be an additional LEFT OUTER JOIN with FillWeight table on related columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jasluv
Starting Member

8 Posts

Posted - 2012-08-03 : 00:57:59

I can only do the LEFT OUTER JOIN on column OrderType of FillWeight, but once adding LEFT OUTER JOIN on Packstyle of FillWeight table, I producded wrong results.

What can I add to this query to properly link the column ordertype, packstyle of FillWeight table to corresponding on ECOTransactions.

SELECT *SelectFields* FROM Marketing.modECOTransactions AS ECO
LEFT OUTER JOIN Production.dimPackStyle AS PCK ON ECO.BSPackstyleID = PCK.PackStyleID
LEFT OUTER JOIN Marketing.dimOrderType OTP ON ECO.OrderTypeID = OTP.OrderTypeID
LEFT OUTER JOIN Marketing.modFillWeightPackstyle AS FWP ON FWP.OrderTypeID = eco.OrderTypeID

sshelper
Posting Yak Master

216 Posts

Posted - 2012-08-03 : 01:12:53

If I understood your question correctly, try to do a LEFT OUTER JOIN between ECOTransactions and modFillWeightPackStyle first then followed by the LEFT OUTER JOIN on the other 2 tables.

SELECT *SelectFields* FROM Marketing.modECOTransactions AS ECO
LEFT OUTER JOIN Marketing.modFillWeightPackstyle AS FWP ON FWP.OrderTypeID = eco.OrderTypeID AND FWP.BSPackstyleID AND eco.BSPackstyleID
LEFT OUTER JOIN Production.dimPackStyle AS PCK ON ECO.BSPackstyleID = PCK.PackStyleID
LEFT OUTER JOIN Marketing.dimOrderType OTP ON ECO.OrderTypeID = OTP.OrderTypeID

Hope this helps.

SQL Server Helper
http://www.sql-server-helper.com/error-messages/msg-1-500.aspx
http://www.sql-server-helper.com/tips/tip-of-the-day.aspx

jasluv
Starting Member

8 Posts

Posted - 2012-08-03 : 01:54:57

I tried that already...but it won't work because on the table FillWeight, there are OrderType that doesn't have a packstyle (Allow Null), so that query will only display does OrderType that has Packstyle and return null value to OrderType that has a NULL Packstyle.

If I can just do a case statement inside the JOINS, because those types of OrderType entry that has no packstyle are those names that contains a word 'LOINS'...and would have this query

SELECT *SelectFields* FROM Marketing.modECOTransactions AS ECO
LEFT OUTER JOIN Marketing.modFillWeightPackstyle AS FWP ON FWP.OrderTypeID = eco.OrderTypeID AND
(CASE WHEN OTP.OrderType like '%LOINS%' THEN OTP. FWP.BSPackstyleID = eco.BSPackstyleID END)
LEFT OUTER JOIN Production.dimPackStyle AS PCK ON ECO.BSPackstyleID = PCK.PackStyleID
LEFT OUTER JOIN Marketing.dimOrderType OTP ON ECO.OrderTypeID = OTP.OrderTypeID

But case statement inside JOIN is not valid in MS SQL.

Please excuse me with my English grammar ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-03 : 02:37:50

[code]SELECT {SelectFields}
FROM Marketing.modECOTransactions AS ECO
LEFT JOIN Marketing.modFillWeightPackstyle AS FWP ON FWP.OrderTypeID = eco.OrderTypeID
AND 1 = CASE
WHEN OTP.OrderType LIKE '%LOINS%' AND OTP. FWP.BSPackstyleID = eco.BSPackstyleID THEN 1
WHEN OTP.OrderType LIKE '%LOINS%' THEN 0
ELSE 1
END
LEFT JOIN Production.dimPackStyle AS PCK ON ECO.BSPackstyleID = PCK.PackStyleID
LEFT JOIN Marketing.dimOrderType OTP ON ECO.OrderTypeID = OTP.OrderTypeID[/code]

N 56°04'39.26"
E 12°55'05.63"

jasluv
Starting Member

8 Posts

Posted - 2012-08-03 : 03:41:02

Thanks for the reply...but it wont work...

I also forgot to mention that once the Packstyle column in FillWeight is null, the Packstyle column in ECOTransaction is also null (OrderType that contains 'LOINS')...

jasluv
Starting Member

8 Posts

Posted - 2012-08-03 : 04:34:10

Please run this script,.. to properly troubleshoot my problem... the end select is my initial query which produced wrong results.

CREATE TABLE [dbo].[dimOrderType](
[OrderTypeID] [int] IDENTITY(1,1) NOT NULL,
[OrderType] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_dimECOOrderType] PRIMARY KEY CLUSTERED
( [OrderTypeID] ASC ))
--==========================================
CREATE TABLE [dbo].[dimPackStyle](
[PackStyleID] [int] IDENTITY(1,1) NOT NULL,
[PackStyle] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_PackStyle] PRIMARY KEY CLUSTERED
( [PackStyleID] ASC ))
--====================================================
CREATE TABLE [dbo].[modECOTransactions](
[ECOID] [int] IDENTITY(1,1) NOT NULL,
[OrderTypeID] [int] NOT NULL,
[BSPackstyleID] [int] NULL,
CONSTRAINT [PK_modECOTransactions] PRIMARY KEY CLUSTERED
( [ECOID] ASC ))
ALTER TABLE [dbo].[modECOTransactions] WITH CHECK ADD CONSTRAINT [FK_modECOTransactions_dimOrderType] FOREIGN KEY([OrderTypeID])
REFERENCES [dbo].[dimOrderType] ([OrderTypeID])
ALTER TABLE [dbo].[modECOTransactions] CHECK CONSTRAINT [FK_modECOTransactions_dimOrderType]
ALTER TABLE [dbo].[modECOTransactions] WITH NOCHECK ADD CONSTRAINT [FK_modECOTransactions_dimPackstyle] FOREIGN KEY([BSPackstyleID])
REFERENCES [dbo].[dimPackStyle] ([PackStyleID])
ALTER TABLE [dbo].[modECOTransactions] CHECK CONSTRAINT [FK_modECOTransactions_dimPackstyle]
--=========================================================
CREATE TABLE [dbo].[modFillWeightPackstyle](
[FillWeightPackstyleID] [int] IDENTITY(1,1) NOT NULL,
[OrderTypeID] [int] NOT NULL,
[PacktyleID] [int] NULL,
[FillWeight] [numeric](18, 3) NOT NULL,
[Variable1] [numeric](18, 2) NOT NULL,
CONSTRAINT [PK_modFillWeightPackstyle] PRIMARY KEY CLUSTERED
([FillWeightPackstyleID] ASC ))
ALTER TABLE [dbo].[modFillWeightPackstyle] WITH CHECK ADD CONSTRAINT [FK_modFillWeightPackstyle_dimOrderType] FOREIGN KEY([OrderTypeID])
REFERENCES [dbo].[dimOrderType] ([OrderTypeID])
ALTER TABLE [dbo].[modFillWeightPackstyle] CHECK CONSTRAINT [FK_modFillWeightPackstyle_dimOrderType]
ALTER TABLE [dbo].[modFillWeightPackstyle] WITH NOCHECK ADD CONSTRAINT [FK_modFillWeightPackstyle_dimPackstyle] FOREIGN KEY([PacktyleID])
REFERENCES [dbo].[dimPackStyle] ([PackStyleID])
NOT FOR REPLICATION
ALTER TABLE [dbo].[modFillWeightPackstyle] NOCHECK CONSTRAINT [FK_modFillWeightPackstyle_dimPackstyle]
--==========================================================
INSERT INTO dbo.dimPackStyle (PackStyle)
VALUES ('SCB11')
INSERT INTO dbo.dimPackStyle (PackStyle)
VALUES ('SCV11')
--=========================================
INSERT INTO dimOrderType (OrderType)
VALUES('Skipjack Catering Packs')
INSERT INTO dimOrderType (OrderType)
VALUES('BONITO LOINS')
--=========================================
INSERT INTO modECOTransactions (OrderTypeID, BSPackstyleID)
values( 2 ,null)
INSERT INTO modECOTransactions (OrderTypeID, BSPackstyleID)
values( 1 , 2 )
--========================================
INSERT INTO modFillWeightPackstyle (OrderTypeID,PacktyleID,FillWeight,Variable1)
VALUES(2, NULL, 6.000, 18.00)
INSERT INTO modFillWeightPackstyle (OrderTypeID,PacktyleID,FillWeight,Variable1)
VALUES(1, 2, 1.100, 0.42)
INSERT INTO modFillWeightPackstyle (OrderTypeID,PacktyleID,FillWeight,Variable1)
VALUES(1, 1, 1.270, 0.42)
--========================================
SELECT ECO.ECOID,ECO.OrderTypeID, ECO.BSPackstyleID,OTP.OrderType,PCK.PackStyle,FWP.PacktyleID,FWP.FillWeight, FWP.Variable1 FROM dbo.modECOTransactions AS ECO
LEFT JOIN dbo.dimPackStyle AS PCK ON ECO.BSPackstyleID = PCK.PackStyleID
LEFT JOIN dbo.dimOrderType AS OTP ON ECO.OrderTypeID = OTP.OrderTypeID
LEFT JOIN dbo.modFillWeightPackstyle AS FWP ON FWP.OrderTypeID = eco.OrderTypeID
--??? Problem Linking the PackStyle to modECOtransaction.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 10:28:37

sorry still didnt understand what issue is

this is result that i got from your query. isnt this what you expected? if not, give your expected output


ECOID	OrderTypeID	BSPackstyleID	OrderType	         PackStyle	PacktyleID	FillWeight	Variable1
1	2	         NULL	         BONITO LOINS	         NULL	         NULL	         6.000	         18.00
2	1	         2	         Skipjack Catering Packs	SCV11	         2	         1.100	         0.42
2	1	         2	         Skipjack Catering Packs	SCV11	         1	         1.270	         0.42

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jasluv
Starting Member

8 Posts

Posted - 2012-08-03 : 23:44:01

The results should only be 2 rows..since my main table ECOTransction has only 2 rows of data...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 23:47:39

ok..so when you make it into one row, what should be the values you need to see for PacktyleID,FillWeight etc as they obviously have different values in modFillWeightPackstyle table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jasluv
Starting Member

8 Posts

Posted - 2012-08-04 : 20:53:39

The values display on the ECOTransaction should corresponds to all the values in modFillWeightPackstyle Table (FillWeight,Variable1) base on the relationship of OrderTypeID and PackStyle, since the data contains in modFillWeightPackstyle column PackstyleID and OrdertypeID is equal to what the modECOtransaction column PackstyleID and OrderTypeID contains. So my problem is how to connect this two column (PackstyleID and OrderTypeID).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-05 : 14:55:43

then obviously you'll have multiple records for some of the OrderTypes as there are multiple packtyles associated with same OrderTypeID. So either you'll get multiple records or if you want one per OrderType you need to apply some kind of aggregation over it to make them one

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources