Urgent! SQL Query Help - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Urgent! SQL Query Help

Author

Topic

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-07-27 : 15:32:35

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

PrintUnitNumber PrintUnitName InkID
1 Cyan 100
2 Magenta 101
3 Yellow 102
5 Gray 105
6 Red 106

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-27 : 21:38:16

In PrintUnits table, you have 2 record with PrintUnitNumber = 1 is this a mistake ?

Is your sample data complete ? There is no data for InkID 100 to 103 in Inks table

Try this

SELECT	p.PrintUnitNumber, p.PrintUnitName, i.InkID
FROM 	PrintUnits p
	INNER JOIN 
	(
		SELECT	i.InkID, 
			Ink1 = u1.InkUnitID, i.Ink1Percentage,
			Ink2 = u2.InkUnitID, i.Ink2Percentage,
			Ink3 = u3.InkUnitID, i.Ink3Percentage
		FROM	Inks i
			left JOIN InkUnits u1	ON	i.Ink1	= u1.InkID
			left JOIN InkUnits u2	ON	i.Ink2	= u2.InkID
			left JOIN InkUnits u3	ON	i.Ink3	= u3.InkID
	) i	
	ON	(
			(p.InkUnit1	= i.Ink1	AND	p.InkUnit1Per	= i.Ink1Percentage)
		OR	p.InkUnit1	IS NULL
		)
	AND	(
			(p.InkUnit2	= i.Ink2	AND	p.InkUnit2Per	= i.Ink2Percentage)
		OR	p.InkUnit2	IS NULL
		)
	AND	(
			(p.InkUnit3	= i.Ink3	AND	p.InkUnit3Per	= i.Ink3Percentage)
		OR	p.InkUnit3	IS NULL
		)

KH
[spoiler]Time is always against us[/spoiler]

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-07-27 : 22:32:07

Khtan,

>>In PrintUnits table, you have 2 record with PrintUnitNumber = 1 is this a mistake ?
My mistake it need to 7 or whatever comes next

There is no data for InkID 100 to 103 in Inks table
>>There is a reason for this. In printing industry (books, magazines etc. [url]http://en.wikipedia.org/wiki/CMYK[/url] color model is used. Cyan, Magenta, Yellow and Black are kind of primary color. So there is no value for InkID1, InkID2 and InkID3 and their percentages.

While Red color can be achieve by combining Magenta and Yellow.

PrintUnitNumer PrintUnitName InkID
1 Cyan 100
2 Magenta 101
3 Yellow 102
4 Black 103
5 Gray 105
6 Red 106

I spent close to three days figuring this out.. You are close. Is there any way I can get result in the above format

Thanks much

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-27 : 22:52:41

Then why in table PrintUnits, for PrintUnitNumber = 1, InkUnit1 is not NULL ?

SELECT	p.PrintUnitNumber, p.PrintUnitName, InkID = coalesce(i.InkID, u.InkID)
FROM 	PrintUnits p
	LEFT JOIN 
	(
		SELECT	i.InkID, 
			Ink1 = u1.InkUnitID, i.Ink1Percentage,
			Ink2 = u2.InkUnitID, i.Ink2Percentage,
			Ink3 = u3.InkUnitID, i.Ink3Percentage
		FROM	Inks i
			left JOIN InkUnits u1	ON	i.Ink1	= u1.InkID
			left JOIN InkUnits u2	ON	i.Ink2	= u2.InkID
			left JOIN InkUnits u3	ON	i.Ink3	= u3.InkID
	) i	
	ON	(
			(p.InkUnit1	= i.Ink1	AND	p.InkUnit1Per	= i.Ink1Percentage)
		OR	p.InkUnit1	IS NULL
		)
	AND	(
			(p.InkUnit2	= i.Ink2	AND	p.InkUnit2Per	= i.Ink2Percentage)
		OR	p.InkUnit2	IS NULL
		)
	AND	(
			(p.InkUnit3	= i.Ink3	AND	p.InkUnit3Per	= i.Ink3Percentage)
		OR	p.InkUnit3	IS NULL
		)
	left JOIN InkUnits u	ON	p.InkUnit1	= u.InkUnitID

KH
[spoiler]Time is always against us[/spoiler]

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2008-07-28 : 08:20:15

Thank you. I appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources