May 25, 2010 at 3:50 pm
I have added a table to an existing ecommerce system and am having troubles understanding joins. So here it is.
four tables:
ProductsOrdered (relations: idOrder idProduct)
products (relations: idProduct)
orders (relations: idOrder
licensing (relations: idOrder, idProduct)
Here is what I am trying to do.
the query gets passed a variable which is the id of the order.
query the order table for id
join productsordered at id get back 1 or more idproducts
join products table for each idproduct get sku and description
join licensing table for each idproduct and get 0 or more licensestatus.
I have put together this code but its not working correctly
SELECT p.description, p.sku, po.idorder, po.quantity, o.idorder, l.licensestatus
FROM ProductsOrdered AS po
JOIN orders AS o
ON o.idorder=po.idOrder AND o.idorder=81
JOIN products AS p
ON po.idProduct=p.idProduct
JOIN licensing AS l on l.idProduct=po.idProduct AND o.idorder = l.idorder
I have swapped the joins around and I just cant wrap my brain around this. Thanks in advance for any help
May 25, 2010 at 6:59 pm
In what way is it not working correctly ?
May 25, 2010 at 7:59 pm
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 25, 2010 at 9:20 pm
Thanks for squaring me away, I will revise my question.
May 25, 2010 at 11:44 pm
Orders Table:
USE [Database]
GO
/****** Object: Table [dbo].[orders] Script Date: 05/25/2010 22:52:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[orders](
[idOrder] [int] IDENTITY(1,1) NOT NULL,
[orderDate] [datetime] NULL,
[idCustomer] [int] NULL CONSTRAINT [DF__orders__idCustom__15502E78] DEFAULT ((0)),
[details] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [aaaaaorders_PK] PRIMARY KEY NONCLUSTERED
(
[idOrder] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ProductsOrdered Table:
USE [Database]
GO
/****** Object: Table [dbo].[ProductsOrdered] Script Date: 05/25/2010 22:55:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductsOrdered](
[idProductOrdered] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[idOrder] [int] NULL CONSTRAINT [DF__ProductsO__idOrd__0169315C] DEFAULT ((0)),
[idProduct] [int] NULL CONSTRAINT [DF__ProductsO__idPro__025D5595] DEFAULT ((0)),
[service] [bit] NOT NULL CONSTRAINT [DF__ProductsO__servi__035179CE] DEFAULT ((0)),
[quantity] [int] NULL CONSTRAINT [DF__ProductsO__quant__04459E07] DEFAULT ((0)),
CONSTRAINT [aaaaaProductsOrdered_PK] PRIMARY KEY NONCLUSTERED
(
[idProductOrdered] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Products Table:
USE [database]
GO
/****** Object: Table [dbo].[products] Script Date: 05/25/2010 22:56:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[products](
[idProduct] [int] IDENTITY(1,1) NOT NULL,
[description] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sku] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [aaaaaproducts_PK] PRIMARY KEY NONCLUSTERED
(
[idProduct] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Licensing Table:
USE [database]
GO
/****** Object: Table [dbo].[licensing] Script Date: 05/25/2010 22:48:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[licensing](
[licensid] [int] IDENTITY(1,1) NOT NULL,
[idProduct] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[licensestatus] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[idorder] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
I updated the code I missed a value. The query is not returning a value from the licensing table.
SELECT p.description, p.sku,o.idorder, po.idorder, po.quantity, l.licensestatus
FROM ProductsOrdered AS po
JOIN orders AS o
ON o.idorder=po.idOrder AND o.idorder=89
JOIN products AS p
ON po.idProduct=p.idProduct
JOIN licensing AS l on po.idProduct=l.idProduct AND o.idorder = l.idorder
I think IM just mixing up on the order of my joins.
Im going to try and revise my question as advised by Wayne and see if I can make this more clear.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply