Select Query Asstiance

Question

SQL Server 2016 here...I'm wanting to return all rows from my products table, then return only the info that meets the where criteria. Currently, I am only getting data returned where the count >= 1 so if an item exists in my product table but nothing was ordered that product is not being returned.

Below is DDL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderItems](
[ID] [int] IDENTITY(-2147483648,1) NOT NULL,
[OrderID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
[Quantity] [int] NOT NULL,
[FileUploadID] [int] NOT NULL,
CONSTRAINT [PK_OrderItems] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Orders] Script Date: 5/10/2021 6:46:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FileUploadID] [int] NOT NULL,
[EmployeeID] [int] NOT NULL,
[StoreID] [int] NOT NULL,
[ShippingDestinationID] [int] NOT NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Products] Script Date: 5/10/2021 6:46:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Code] nvarchar NOT NULL,
[Name] nvarchar NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[OrderItems] ON
GO
INSERT [dbo].[OrderItems] ([ID], [OrderID], [ProductID], [Quantity], [FileUploadID]) VALUES (-2147483427, 48, 8739, 1, 26)
GO
INSERT [dbo].[OrderItems] ([ID], [OrderID], [ProductID], [Quantity], [FileUploadID]) VALUES (-2147483426, 48, 8740, 1, 26)
GO
INSERT [dbo].[OrderItems] ([ID], [OrderID], [ProductID], [Quantity], [FileUploadID]) VALUES (-2147483425, 49, 8740, 1, 26)
GO
INSERT [dbo].[OrderItems] ([ID], [OrderID], [ProductID], [Quantity], [FileUploadID]) VALUES (-2147483424, 50, 8740, 2, 26)
GO
SET IDENTITY_INSERT [dbo].[OrderItems] OFF
GO
SET IDENTITY_INSERT [dbo].[Orders] ON
GO
INSERT [dbo].[Orders] ([ID], [FileUploadID], [EmployeeID], [StoreID], [ShippingDestinationID]) VALUES (47, 26, 30, 30, 43)
GO
INSERT [dbo].[Orders] ([ID], [FileUploadID], [EmployeeID], [StoreID], [ShippingDestinationID]) VALUES (48, 26, 28, 28, 25)
GO
INSERT [dbo].[Orders] ([ID], [FileUploadID], [EmployeeID], [StoreID], [ShippingDestinationID]) VALUES (49, 26, 29, 29, 36)
GO
INSERT [dbo].[Orders] ([ID], [FileUploadID], [EmployeeID], [StoreID], [ShippingDestinationID]) VALUES (50, 26, 30, 30, 43)
GO
SET IDENTITY_INSERT [dbo].[Orders] OFF
GO
SET IDENTITY_INSERT [dbo].[Products] ON
GO
INSERT [dbo].[Products] ([ID], [Code], [Name]) VALUES (8671, N'12 PACKS')
GO
INSERT [dbo].[Products] ([ID], [Code], [Name]) VALUES (8672, N'02', N'62oz')
GO
INSERT [dbo].[Products] ([ID], [Code], [Name]) VALUES (8673, N'03', N'Battery Stickers')
GO
INSERT [dbo].[Products] ([ID], [Code], [Name]) VALUES (8674, N'4', N'Sticker Sheets')
GO
INSERT [dbo].[Products] ([ID], [Code], [Name]) VALUES (8675, N'5', N'Code Sticker')
GO
INSERT [dbo].[Products] ([ID], [Code], [Name]) VALUES (8704, N'02', N'Clips')
GO
INSERT [dbo].[Products] ([ID], [Code], [Name]) VALUES (8705, N'26', N'Strips')
GO
INSERT [dbo].[Products] ([ID], [Code], [Name]) VALUES (8706, N'H', N'Tabs')
GO
GO
SET IDENTITY_INSERT [dbo].[Products] OFF
GO
ALTER TABLE [dbo].[OrderItems] WITH NOCHECK ADD CONSTRAINT [FK_OrderItems_FileUploads] FOREIGN KEY([FileUploadID])
REFERENCES [dbo].[FileUploads] ([ID])
GO
ALTER TABLE [dbo].[OrderItems] CHECK CONSTRAINT [FK_OrderItems_FileUploads]
GO
ALTER TABLE [dbo].[OrderItems] WITH NOCHECK ADD CONSTRAINT [FK_OrderItems_Orders] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([ID])
GO
ALTER TABLE [dbo].[OrderItems] CHECK CONSTRAINT [FK_OrderItems_Orders]
GO
ALTER TABLE [dbo].[OrderItems] WITH NOCHECK ADD CONSTRAINT [FK_OrderItems_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[OrderItems] CHECK CONSTRAINT [FK_OrderItems_Products]
GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_Employees] FOREIGN KEY([EmployeeID])
REFERENCES [dbo].[Employees] ([ID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Employees]
GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_FileUploads] FOREIGN KEY([FileUploadID])
REFERENCES [dbo].[FileUploads] ([ID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_FileUploads]
GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_ShippingDestinations] FOREIGN KEY([ShippingDestinationID])
REFERENCES [dbo].[ShippingDestinations] ([ID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_ShippingDestinations]
GO
ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_Stores] FOREIGN KEY([StoreID])
REFERENCES [dbo].[Stores] ([ID])
GO
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Stores]
GO

I tried the query ->

 SELECT
    p.[ID],
    p.[Code],
    p.[Name],
    oi.FileUploadID
FROM Products p
LEFT join OrderItems oi
on oi.ProductID = p.id
LEFT JOIN Orders o
ON o.ID = oi.OrderID 
AND o.FileUploadID = oi.FileUploadID
WHERE o.FileUploadID = 26

I was thinking since I selected from product first then did left joins on all the tables it would do that, but no luck.

Answer 1

Hi @Johnathan Simpson ，

Thank you for posting here in Microsoft Q&A.

Your problem seemed to be a data issue.

You used ProductID from OrderItems table and ID from Products table to join together, but there was no proper data available which meant there was no join criteria. So even if you did many left joins or other joins, there was no data reported.

You could have a try to insert one proper row of data into Products table like below:

SET IDENTITY_INSERT [dbo].[Products] ON  
GO  
INSERT [dbo].[Products] ([ID], [Code], [Name]) VALUES (8739, N'11',N'PACKS')  
GO  
SET IDENTITY_INSERT [dbo].[Products] OFF  
GO  

Then you could have one row of data reported after executing your query.

SELECT  
     p.[ID],  
     p.[Code],  
     p.[Name],  
     oi.FileUploadID  
 FROM Products p  
 LEFT join OrderItems oi  
 on oi.ProductID = p.id  
 LEFT JOIN Orders o  
 ON o.ID = oi.OrderID   
 AND o.FileUploadID = oi.FileUploadID  
 WHERE o.FileUploadID = 26  

Output:

ID Code Name FileUploadID  
8739 11 PACKS 26  

If above is still not working, we recommend that you post more sample data and expected result of the sample.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Johnathan Simpson ,

Your DDL has lots of errors due to references to not needed tables.
There are the errors:

Msg 1767, Level 16, State 0, Line 89  
Foreign key 'FK_OrderItems_FileUploads' references invalid table 'dbo.FileUploads'.  
Msg 1750, Level 16, State 1, Line 89  
Could not create constraint or index. See previous errors.  
Msg 4917, Level 16, State 0, Line 92  
Constraint 'FK_OrderItems_FileUploads' does not exist.  
Msg 4916, Level 16, State 0, Line 92  
Could not enable or disable the constraint. See previous errors.  
Msg 1767, Level 16, State 0, Line 105  
Foreign key 'FK_Orders_Employees' references invalid table 'dbo.Employees'.  
Msg 1750, Level 16, State 1, Line 105  
Could not create constraint or index. See previous errors.  
Msg 4917, Level 16, State 0, Line 108  
Constraint 'FK_Orders_Employees' does not exist.  
Msg 4916, Level 16, State 0, Line 108  
Could not enable or disable the constraint. See previous errors.  
Msg 1767, Level 16, State 0, Line 110  
Foreign key 'FK_Orders_FileUploads' references invalid table 'dbo.FileUploads'.  
Msg 1750, Level 16, State 1, Line 110  
Could not create constraint or index. See previous errors.  
Msg 4917, Level 16, State 0, Line 113  
Constraint 'FK_Orders_FileUploads' does not exist.  
Msg 4916, Level 16, State 0, Line 113  
Could not enable or disable the constraint. See previous errors.  
Msg 1767, Level 16, State 0, Line 115  
Foreign key 'FK_Orders_ShippingDestinations' references invalid table 'dbo.ShippingDestinations'.  
Msg 1750, Level 16, State 1, Line 115  
Could not create constraint or index. See previous errors.  
Msg 4917, Level 16, State 0, Line 118  
Constraint 'FK_Orders_ShippingDestinations' does not exist.  
Msg 4916, Level 16, State 0, Line 118  
Could not enable or disable the constraint. See previous errors.  
Msg 1767, Level 16, State 0, Line 120  
Foreign key 'FK_Orders_Stores' references invalid table 'dbo.Stores'.  
Msg 1750, Level 16, State 1, Line 120  
Could not create constraint or index. See previous errors.  
Msg 4917, Level 16, State 0, Line 123  
Constraint 'FK_Orders_Stores' does not exist.  
Msg 4916, Level 16, State 0, Line 123  
Could not enable or disable the constraint. See previous errors.  

Additionally, your sample data population has issues with actual data.

In any case, here is a correct query to join three table in question. I had to use LEFT OUTER JOINs due to actual data issues.

SQL

SELECT *   
FROM dbo.Orders AS o LEFT OUTER JOIN   
	dbo.OrderItems AS oi   
		ON o.ID = oi.OrderID LEFT OUTER JOIN   
	dbo.Products AS p ON oi.ProductID = p.ID;