July 27, 2008 at 2:08 pm
[font="Courier New"]Using SQL 2000
Here is the DDL and some sample data
USE TempDb
Go
--Inks table consists of inks that can be made by combining cyan, magenta, yellow and black
--e.g. Yellow and Magenta mixed together gives Red
CREATE TABLE [dbo].[Inks](
[InkID] [int] NOT NULL,
[InkName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Ink1] [int] NULL,
[Ink1Percentage] [int] NULL,
[Ink2] [int] NULL,
[Ink2Percentage] [int] NULL,
[Ink3] [int] NULL,
[Ink3Percentage] [int] NULL,
CONSTRAINT [PK_Inks] PRIMARY KEY CLUSTERED
(
[InkID] ASC
)
)
GO
INSERT [dbo].[Inks] ([InkID], [InkName], [Ink1], [Ink1Percentage], [Ink2], [Ink2Percentage], [Ink3], [Ink3Percentage]) VALUES (100, 'Cyan', NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Inks] ([InkID], [InkName], [Ink1], [Ink1Percentage], [Ink2], [Ink2Percentage], [Ink3], [Ink3Percentage]) VALUES (101, 'Magenta', NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Inks] ([InkID], [InkName], [Ink1], [Ink1Percentage], [Ink2], [Ink2Percentage], [Ink3], [Ink3Percentage]) VALUES (102, 'Yellow', NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Inks] ([InkID], [InkName], [Ink1], [Ink1Percentage], [Ink2], [Ink2Percentage], [Ink3], [Ink3Percentage]) VALUES (103, 'Black', NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Inks] ([InkID], [InkName], [Ink1], [Ink1Percentage], [Ink2], [Ink2Percentage], [Ink3], [Ink3Percentage]) VALUES (104, 'Cyan_50', 100, 50, NULL, NULL, NULL, NULL)
INSRT [dbo].[Inks] ([InkID], [InkName], [Ink1], [Ink1Percentage], [Ink2], [Ink2Percentage], [Ink3], [Ink3Percentage]) VALUES (105, 'Gray', 100, 50, 101, 40, 102, 40)
INSERT [dbo].[Inks] ([InkID], [InkName], [Ink1], [Ink1Percentage], [Ink2], [Ink2Percentage], [Ink3], [Ink3Percentage]) VALUES (106, 'Red', NULL, NULL, 101, 100, 102, 100)
Go
--Ink Units Table (e.g. Cyan, Magenta, Yellow and Black are ink units
CREATE TABLE [dbo].[InkUnits](
[InkUnitID] [int] NOT NULL,
[InkID] [int] NULL,
[InkUnitNumber] [int] NULL,
[IsUsed] [bit] NOT NULL,
CONSTRAINT [PK_InkUnits] PRIMARY KEY CLUSTERED
(
[InkUnitID] ASC
)
)
GO
INSERT [dbo].[InkUnits] ([InkUnitID], [InkID], [InkUnitNumber], [IsUsed]) VALUES (1, 100, 1, 1)
INSERT [dbo].[InkUnits] ([InkUnitID], [InkID], [InkUnitNumber], [IsUsed]) VALUES (2, 101, 2, 1)
INSERT [dbo].[InkUnits] ([InkUnitID], [InkID], [InkUnitNumber], [IsUsed]) VALUES (3, 102, 3, 1)
INSERT [dbo].[InkUnits] ([InkUnitID], [InkID], [InkUnitNumber], [IsUsed]) VALUES (4, 103, 4, 1)
INSERT [dbo].[InkUnits] ([InkUnitID], [InkID], [InkUnitNumber], [IsUsed]) VALUES (5, NULL, 5, 0)
--PrintUnits is table that use InkUnits to print differnt units (think of it as a unit of print that needed to be done)
CREATE TABLE [dbo].[PrintUnits](
[PrintUnitNumber] [int] NULL,
[PrintUnitName] [varchar](50) NULL,
[InkUnit1] [int] NULL,
[InkUnit1Per] [int] NULL,
[InkUnit2] [int] NULL,
[InkUnit2Per] [int] NULL,
[InkUnit3] [int] NULL,
[InkUnit3Per] [int] NULL
)
GO
INSERT [dbo].[PrintUnits] ([PrintUnitNumber], [PrintUnitName], [InkUnit1], [InkUnit1Per], [InkUnit2], [InkUnit2Per], [InkUnit3], [InkUnit3Per]) VALUES (1, 'Cyan', 1, 100, NULL, NULL, NULL, NULL)
INSERT [dbo].[PrintUnits] ([PrintUnitNumber], [PrintUnitName], [InkUnit1], [InkUnit1Per], [InkUnit2], [InkUnit2Per], [InkUnit3], [InkUnit3Per]) VALUES (2, 'Magenta', 2, 100, NULL, NULL, NULL, NULL)
INSERT [dbo].[PrintUnits] ([PrintUnitNumber], [PrintUnitName], [InkUnit1], [InkUnit1Per], [InkUnit2], [InkUnit2Per], [InkUnit3], [InkUnit3Per]) VALUES (3, 'Yellow', 3, 100, NULL, NULL, NULL, NULL)
INSERT [dbo].[PrintUnits] ([PrintUnitNumber], [PrintUnitName], [InkUnit1], [InkUnit1Per], [InkUnit2], [InkUnit2Per], [InkUnit3], [InkUnit3Per]) VALUES (4, 'Black', 4, 100, NULL, NULL, NULL, NULL)
INSERT [dbo].[PrintUnits] ([PrintUnitNumber], [PrintUnitName], [InkUnit1], [InkUnit1Per], [InkUnit2], [InkUnit2Per], [InkUnit3], [InkUnit3Per]) VALUES (5, 'Gray', 1, 50, 2, 40, 3, 40)
INSERT [dbo].[PrintUnits] ([PrintUnitNumber], [PrintUnitName], [InkUnit1], [InkUnit1Per], [InkUnit2], [InkUnit2Per], [InkUnit3], [InkUnit3Per]) VALUES (6, 'Red', NULL, NULL, 2, 100, 3, 100)
INSERT [dbo].[PrintUnits] ([PrintUnitNumber], [PrintUnitName], [InkUnit1], [InkUnit1Per], [InkUnit2], [InkUnit2Per], [InkUnit3], [InkUnit3Per]) VALUES (1, 'Green', 1, 100, NULL, NULL, 3, 100)
some explanation
Ink Units consists of four basic inks Cyan, Magenta, Yellow, Black
Inks table consists of hundreds of possible ink combining above four ink units
PrintUnits table consists of units that can be printed by using above four inks, I need to find "InkID" from print units based on inkunits used and its percentage. So in order to print a "Red" print unit we need to use Ink unit number 2 and ink unit number 3 at 100%. need to find ink id based on that.
The result should display like this[/font]
PrintUnitNumber PrintUnitName InkID
1 Cyan 100
2 Magenta 101
3 Yellow 102
5 Gray 105
6 Red 106
July 27, 2008 at 11:39 pm
Could you explain the relationship b/w the 3 tables.
How did u get InkID '106'?
What you want as the result is also not clear.
July 29, 2008 at 3:59 pm
Try this, but you'll have to adapt it as I can't create any tables so I had to use table vars:
DECLARE @Inks TABLE (
InkID int NOT NULL,
InkName varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
Ink1 int NULL,
Ink1Percentage int NULL,
Ink2 int NULL,
Ink2Percentage int NULL,
Ink3 int NULL,
Ink3Percentage int NULL,
PRIMARY KEY (InkID ASC)
)
INSERT INTO @Inks (InkID, InkName, Ink1, Ink1Percentage, Ink2, Ink2Percentage, Ink3, Ink3Percentage)
SELECT 100, 'Cyan', NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
SELECT 101, 'Magenta', NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
SELECT 102, 'Yellow', NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
SELECT 103, 'Black', NULL, NULL, NULL, NULL, NULL, NULL UNION ALL
SELECT 104, 'Cyan_50', 100, 50, NULL, NULL, NULL, NULL UNION ALL
SELECT 105, 'Gray', 100, 50, 101, 40, 102, 40 UNION ALL
SELECT 106, 'Red', NULL, NULL, 101, 100, 102, 100
--Ink Units Table (e.g. Cyan, Magenta, Yellow and Black are ink units
DECLARE @InkUnits TABLE (
InkUnitID int NOT NULL,
InkID int NULL,
InkUnitNumber int NULL,
IsUsed bit NOT NULL,
PRIMARY KEY (InkUnitID ASC)
)
INSERT INTO @InkUnits (InkUnitID, InkID, InkUnitNumber, IsUsed)
SELECT 1, 100, 1, 1 UNION ALL
SELECT 2, 101, 2, 1 UNION ALL
SELECT 3, 102, 3, 1 UNION ALL
SELECT 4, 103, 4, 1 UNION ALL
SELECT 5, NULL, 5, 0
--PrintUnits is table that use InkUnits to print differnt units (think of it as a unit of print that needed to be done)
DECLARE @PrintUnits TABLE (
PrintUnitNumber int NULL,
PrintUnitName varchar(50) NULL,
InkUnit1 int NULL,
InkUnit1Per int NULL,
InkUnit2 int NULL,
InkUnit2Per int NULL,
InkUnit3 int NULL,
InkUnit3Per int NULL
)
INSERT INTO @PrintUnits (PrintUnitNumber, PrintUnitName, InkUnit1, InkUnit1Per, InkUnit2, InkUnit2Per, InkUnit3, InkUnit3Per)
SELECT 1, 'Cyan', 1, 100, NULL, NULL, NULL, NULL UNION ALL
SELECT 2, 'Magenta', 2, 100, NULL, NULL, NULL, NULL UNION ALL
SELECT 3, 'Yellow', 3, 100, NULL, NULL, NULL, NULL UNION ALL
SELECT 4, 'Black', 4, 100, NULL, NULL, NULL, NULL UNION ALL
SELECT 5, 'Gray', 1, 50, 2, 40, 3, 40 UNION ALL
SELECT 6, 'Red', NULL, NULL, 2, 100, 3, 100 UNION ALL
SELECT 7, 'Green', 1, 100, NULL, NULL, 3, 100
;WITH THENUMS AS (
SELECT CAST(1 AS smallint) AS XNUM UNION ALL
SELECT 2 UNION ALL
SELECT 3
),
PUNITS AS (
SELECT PrintUnitNumber, PrintUnitName,
CASE XNUM
WHEN 1 THEN InkUnit1
WHEN 2 THEN InkUnit2
WHEN 3 THEN InkUnit3
END AS InkUnitNumber,
CASE XNUM
WHEN 1 THEN InkUnit1Per
WHEN 2 THEN InkUnit2Per
WHEN 3 THEN InkUnit3Per
END AS InkUnitPer
FROM @PrintUnits AS PU, THENUMS
),
INKUNITS AS (
SELECT *
FROM @InkUnits
),
INKS AS (
SELECT *
FROM @Inks
)
SELECT DISTINCT PUNITS.PrintUnitNumber, PUNITS.PrintUnitName, INKS.InkId
FROM PUNITS LEFT OUTER JOIN INKUNITS
ON PUNITS.InkUnitNumber=INKUNITS.InkUnitNumber
LEFT OUTER JOIN INKS
ON PUNITS.PrintUnitName=INKS.InkName
WHERE PUNITS.InkUnitNumber IS NOT NULL AND
PUNITS.InkUnitPer IS NOT NULL AND
INKS.InkId IS NOT NULL
Oops... just realized this is a SQL 7,2000 forum. You'll also have to convert out of the CTE's into derived tables. I don't have the time right now. Not even sure this is what you want, but it appears to work. I couldn't be sure of how to join to the Inks table. Hopefully, this at least get's you going in the right direction.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 30, 2008 at 7:29 am
Thanks Steve.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply