question

InigoMontoya-1790 avatar image
0 Votes"
InigoMontoya-1790 asked MelissaMa-msft commented

Select Query Asstiance

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](50) NOT NULL,
[Name] [nvarchar](170) 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.



sql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@InigoMontoya-1790,

You asking questions on this forum without providing a feedback and marking proposed solutions as Accepted Answer. For example: split-field-on-space-if-exists

It makes your post useless for other folks who are looking for answers, and disrespectful for folks who spent their time to provide you solutions.


0 Votes 0 ·

@YitzhakKhabinsky-0887 - My apalogy, i had thought i accepted an answer. I always do my best to upvote and accept answers. I will do better moving forward.

0 Votes 0 ·

Hi @InigoMontoya-1790

Could you please validate all the answers so far and provide any update?

If all of them are not working or helpful, please provide more sample data or details about your issue.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @InigoMontoya-1790

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.

95360-order.png

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.


order.png (15.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 published

Hi @InigoMontoya-1790,

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;

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.