SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ymamalis
Starting Member

42 Posts

Posted - 02/17/2013 :  17:51:01  Show Profile  Visit ymamalis's Homepage  Reply with Quote
hi i have a table of products that have some properties
ProductID , PropertyID
1 1
2 1
3 1
1 2
7 2
1 6

and i want a query to give me the products that have all of the properties that are in a list ( for example if i want a product that have propoerty=6 and property=1 then it should return to me only productID=1)
Can you please help me with that ??

James K
Flowing Fount of Yak Knowledge

3651 Posts

Posted - 02/17/2013 :  20:26:04  Show Profile  Reply with Quote
How is the list of properties made available to you? How you would query depends on that. Assuming that it is in a table variable, here is one possible solution:
CREATE TABLE #tmp(productid INT, propertyid INT);
INSERT INTO #tmp VALUES (1,1),(2,1),(1,2),(7,2),(1,6);

DECLARE @propertyIds TABLE (propertyId INT);
INSERT INTO @propertyIds VALUES (1),(6);

DECLARE @PropertyIdCount INT;
SELECT @PropertyIdCount = COUNT(DISTINCT propertyId) FROM @propertyIds;
SELECT 
	ProductId
FROM
	#tmp p
	INNER JOIN @propertyIds i ON i.propertyId = p.PropertyId
GROUP BY
	ProductId
HAVING
	COUNT(DISTINCT p.PropertyId) = @PropertyIdCount

DROP TABLE #tmp;

If the list is in a comma-separated list, you can split it into a virtual table using string splitter functions (available if you google), or you can do something like shown below
DECLARE @propertyIds VARCHAR(128) = '1,6';
DECLARE @PropertyIdCount INT;
SET @PropertyIdCount = LEN(@propertyIds)-LEN(REPLACE(@propertyIds,',',''))+1;
SELECT 
	ProductId 
FROM
	#tmp p
WHERE
	','+@propertyIds+',' LIKE '%,'+ CAST(p.PropertyId AS VARCHAR(32))+ ',%'
GROUP BY
	ProductId
HAVING
	COUNT(DISTINCT p.PropertyId) = @PropertyIdCount;
Go to Top of Page

ymamalis
Starting Member

42 Posts

Posted - 02/18/2013 :  03:37:17  Show Profile  Visit ymamalis's Homepage  Reply with Quote
Thanks YOU very much for your reply . it helped me a lot.i have one more minor problem. below is the query i created with your help.
i also need to have one more row to be displayed (AddonProductID) but i can't because when i add it in the group by , i am loosing the results.


SELECT p.ProductID
FROM (SELECT ProductID, CharacteristicID, 0 AS AddonProductID
FROM ProductsCharacteristics
UNION
SELECT Products_addons.ProductID, Products_addons.AddonProductID, ProductsCharacteristics_1.CharacteristicID
FROM ProductsCharacteristics AS ProductsCharacteristics_1 LEFT OUTER JOIN
Products_addons ON ProductsCharacteristics_1.ProductID = Products_addons.AddonProductID
WHERE (Products_addons.ProductID IS NOT NULL)) AS p INNER JOIN
(SELECT CharacteristicID
FROM CharacteristicLookup
WHERE (CharacteristicID IN (1, 5, 6))) AS i ON i.CharacteristicID = p.CharacteristicID
GROUP BY p.ProductID
HAVING (COUNT(DISTINCT p.CharacteristicID) =
(SELECT COUNT(DISTINCT CharacteristicID) AS PropertyIDCount
FROM CharacteristicLookup AS CharacteristicLookup_1
WHERE (CharacteristicID IN (1, 5, 6))))
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  03:48:01  Show Profile  Reply with Quote
there are few issues with your posted code

1. UNION requires corresponding columns to be same. you've columns specified in different order in two selects. it should be

SELECT ProductID, CharacteristicID, 0 AS AddonProductID
FROM ProductsCharacteristics
UNION
SELECT Products_addons.ProductID, ProductsCharacteristics_1.CharacteristicID,Products_addons.AddonProductID
...

2.I feel it should be UNION ALL as you've hardcoded value in first select and very little chance that you will same set of values coming from second select
3. you've a join as well as a HAVING filter based on CharacteristicLookup. I feel this is redundant

I cant suggest a solution as I dont exactly know what you're trying to achieve

If you could put some sample data and show your intended output out of them, i will be able to show a possible solution

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ymamalis
Starting Member

42 Posts

Posted - 02/18/2013 :  05:42:22  Show Profile  Visit ymamalis's Homepage  Reply with Quote
ok the issue is that i have a product table and a product modules table.
a module is actually a subproduct(it has a record on products table ). every product and subproduct has 1 to many characteristics.
i need to ask what programs have the x1,x2,x3,x4 characteristics and it should display products with that characteristics either as main product or as sub product
below is some scripts of these tables .
Thanks is advance for your precious help


USE [Rater]
GO
/****** Object: Table [dbo].[Products] Script Date: 02/18/2013 12:41:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](
[ProductID] [int] NOT NULL,
[ProductName] [nvarchar](50) NOT NULL,
[ProviderID] [int] NOT NULL,
[isAddon] [bit] NOT NULL,
[Capital] [decimal](18, 0) NOT NULL,
[Notes] [nvarchar](250) NOT NULL,
[EnabledTill] [date] NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[ProductID] 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
INSERT [dbo].[Products] ([ProductID], [ProductName], [ProviderID], [isAddon], [Capital], [Notes], [EnabledTill]) VALUES (1, N'CiTY Ultracare Plus', 1, 0, CAST(2000000 AS Decimal(18, 0)), N'-', CAST(0xB4B20B00 AS Date))
INSERT [dbo].[Products] ([ProductID], [ProductName], [ProviderID], [isAddon], [Capital], [Notes], [EnabledTill]) VALUES (2, N'CiTY Ultracare Comprehenshive', 1, 0, CAST(1000000 AS Decimal(18, 0)), N'-', CAST(0xB4B20B00 AS Date))
INSERT [dbo].[Products] ([ProductID], [ProductName], [ProviderID], [isAddon], [Capital], [Notes], [EnabledTill]) VALUES (3, N'CiTY Ultracare Select', 1, 0, CAST(750000 AS Decimal(18, 0)), N'-', CAST(0xB4B20B00 AS Date))
INSERT [dbo].[Products] ([ProductID], [ProductName], [ProviderID], [isAddon], [Capital], [Notes], [EnabledTill]) VALUES (4, N'CiTY Ultracare Standard', 1, 0, CAST(500000 AS Decimal(18, 0)), N'-', CAST(0xB4B20B00 AS Date))
INSERT [dbo].[Products] ([ProductID], [ProductName], [ProviderID], [isAddon], [Capital], [Notes], [EnabledTill]) VALUES (5, N'CiTY Maternity Add-On', 1, 1, CAST(3000 AS Decimal(18, 0)), N'-', CAST(0xB4B20B00 AS Date))
INSERT [dbo].[Products] ([ProductID], [ProductName], [ProviderID], [isAddon], [Capital], [Notes], [EnabledTill]) VALUES (6, N'BUPA Worldide', 3, 0, CAST(1000000 AS Decimal(18, 0)), N'-', CAST(0xB4B20B00 AS Date))
INSERT [dbo].[Products] ([ProductID], [ProductName], [ProviderID], [isAddon], [Capital], [Notes], [EnabledTill]) VALUES (7, N'Comprehensive', 1, 0, CAST(1000000 AS Decimal(18, 0)), N'', CAST(0xB4B20B00 AS Date))
INSERT [dbo].[Products] ([ProductID], [ProductName], [ProviderID], [isAddon], [Capital], [Notes], [EnabledTill]) VALUES (8, N'Prestige Plus', 2, 0, CAST(0 AS Decimal(18, 0)), N'', CAST(0xB4B20B00 AS Date))
INSERT [dbo].[Products] ([ProductID], [ProductName], [ProviderID], [isAddon], [Capital], [Notes], [EnabledTill]) VALUES (9, N'Prestige', 2, 0, CAST(2000000 AS Decimal(18, 0)), N'', CAST(0xB4B20B00 AS Date))
INSERT [dbo].[Products] ([ProductID], [ProductName], [ProviderID], [isAddon], [Capital], [Notes], [EnabledTill]) VALUES (10, N'Comprehenshive', 2, 0, CAST(0 AS Decimal(18, 0)), N'', CAST(0xB4B20B00 AS Date))
INSERT [dbo].[Products] ([ProductID], [ProductName], [ProviderID], [isAddon], [Capital], [Notes], [EnabledTill]) VALUES (11, N'Standard', 2, 0, CAST(0 AS Decimal(18, 0)), N'', CAST(0xB4B20B00 AS Date))
INSERT [dbo].[Products] ([ProductID], [ProductName], [ProviderID], [isAddon], [Capital], [Notes], [EnabledTill]) VALUES (12, N'European', 2, 0, CAST(0 AS Decimal(18, 0)), N'', CAST(0xB4B20B00 AS Date))
INSERT [dbo].[Products] ([ProductID], [ProductName], [ProviderID], [isAddon], [Capital], [Notes], [EnabledTill]) VALUES (13, N'??e??e?t???', 4, 0, CAST(0 AS Decimal(18, 0)), N'', CAST(0xB4B20B00 AS Date))
INSERT [dbo].[Products] ([ProductID], [ProductName], [ProviderID], [isAddon], [Capital], [Notes], [EnabledTill]) VALUES (14, N'Travel Plan', 1, 1, CAST(2000000 AS Decimal(18, 0)), N'', CAST(0xB4B20B00 AS Date))
/****** Object: Table [dbo].[CharacteristicLookup] Script Date: 02/18/2013 12:41:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CharacteristicLookup](
[CharacteristicID] [int] NOT NULL,
[CharacteristicEngineID] [int] NOT NULL,
[CharacteristicName] [nvarchar](50) NOT NULL,
[isBasic] [bit] NOT NULL,
[isForCalculation] [bit] NOT NULL,
CONSTRAINT [PK_CharacteristicLookup_1] PRIMARY KEY CLUSTERED
(
[CharacteristicID] 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
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'if not it will be displayed only on comparison list' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CharacteristicLookup', @level2type=N'COLUMN',@level2name=N'isForCalculation'
GO
INSERT [dbo].[CharacteristicLookup] ([CharacteristicID], [CharacteristicEngineID], [CharacteristicName], [isBasic], [isForCalculation]) VALUES (1, 1, N'InPatient', 1, 1)
INSERT [dbo].[CharacteristicLookup] ([CharacteristicID], [CharacteristicEngineID], [CharacteristicName], [isBasic], [isForCalculation]) VALUES (2, 1, N'Doctor Visits', 0, 1)
INSERT [dbo].[CharacteristicLookup] ([CharacteristicID], [CharacteristicEngineID], [CharacteristicName], [isBasic], [isForCalculation]) VALUES (3, 1, N'Medicines', 0, 1)
INSERT [dbo].[CharacteristicLookup] ([CharacteristicID], [CharacteristicEngineID], [CharacteristicName], [isBasic], [isForCalculation]) VALUES (4, 1, N'Diagnostic Exams', 0, 1)
INSERT [dbo].[CharacteristicLookup] ([CharacteristicID], [CharacteristicEngineID], [CharacteristicName], [isBasic], [isForCalculation]) VALUES (5, 1, N'Maternity', 0, 1)
INSERT [dbo].[CharacteristicLookup] ([CharacteristicID], [CharacteristicEngineID], [CharacteristicName], [isBasic], [isForCalculation]) VALUES (6, 1, N'Dental', 0, 1)
INSERT [dbo].[CharacteristicLookup] ([CharacteristicID], [CharacteristicEngineID], [CharacteristicName], [isBasic], [isForCalculation]) VALUES (7, 1, N'Travel Plan', 0, 1)
/****** Object: Table [dbo].[ProductsCharacteristics] Script Date: 02/18/2013 12:41:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProductsCharacteristics](
[CharacteristicID] [int] NOT NULL,
[ProductID] [int] NOT NULL,
CONSTRAINT [PK_ProductsCharacteristics] PRIMARY KEY CLUSTERED
(
[CharacteristicID] ASC,
[ProductID] 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
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (1, 1)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (1, 2)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (1, 3)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (1, 4)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (1, 6)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (2, 1)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (2, 2)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (2, 3)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (3, 1)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (3, 2)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (3, 3)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (4, 1)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (4, 2)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (4, 3)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (5, 5)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (5, 6)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (6, 1)
INSERT [dbo].[ProductsCharacteristics] ([CharacteristicID], [ProductID]) VALUES (7, 1)
/****** Object: Table [dbo].[Products_addons] Script Date: 02/18/2013 12:41:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products_addons](
[ProductID] [int] NOT NULL,
[AddonProductID] [int] NOT NULL,
CONSTRAINT [PK_Products_addons] PRIMARY KEY CLUSTERED
(
[ProductID] ASC,
[AddonProductID] 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
INSERT [dbo].[Products_addons] ([ProductID], [AddonProductID]) VALUES (1, 5)
INSERT [dbo].[Products_addons] ([ProductID], [AddonProductID]) VALUES (1, 14)
/****** Object: Default [DF_CharacteristicLookup_isBasic] Script Date: 02/18/2013 12:41:39 ******/
ALTER TABLE [dbo].[CharacteristicLookup] ADD CONSTRAINT [DF_CharacteristicLookup_isBasic] DEFAULT ((0)) FOR [isBasic]
GO
/****** Object: Default [DF_CharacteristicLookup_isForCalculation] Script Date: 02/18/2013 12:41:39 ******/
ALTER TABLE [dbo].[CharacteristicLookup] ADD CONSTRAINT [DF_CharacteristicLookup_isForCalculation] DEFAULT ((1)) FOR [isForCalculation]
GO
/****** Object: Default [DF_Products_isAddon] Script Date: 02/18/2013 12:41:39 ******/
ALTER TABLE [dbo].[Products] ADD CONSTRAINT [DF_Products_isAddon] DEFAULT ((0)) FOR [isAddon]
GO
/****** Object: Default [DF_Products_Capital] Script Date: 02/18/2013 12:41:39 ******/
ALTER TABLE [dbo].[Products] ADD CONSTRAINT [DF_Products_Capital] DEFAULT ((0)) FOR [Capital]
GO
/****** Object: Default [DF_Products_Notes] Script Date: 02/18/2013 12:41:39 ******/
ALTER TABLE [dbo].[Products] ADD CONSTRAINT [DF_Products_Notes] DEFAULT (N'-') FOR [Notes]
GO
/****** Object: Default [DF_Products_EnabledTill] Script Date: 02/18/2013 12:41:39 ******/
ALTER TABLE [dbo].[Products] ADD CONSTRAINT [DF_Products_EnabledTill] DEFAULT ('1/1/2100') FOR [EnabledTill]
GO
/****** Object: ForeignKey [FK_CharacteristicLookup_RaterEngines] Script Date: 02/18/2013 12:41:39 ******/
ALTER TABLE [dbo].[CharacteristicLookup] WITH CHECK ADD CONSTRAINT [FK_CharacteristicLookup_RaterEngines] FOREIGN KEY([CharacteristicEngineID])
REFERENCES [dbo].[RaterEngines] ([RaterEngineID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CharacteristicLookup] CHECK CONSTRAINT [FK_CharacteristicLookup_RaterEngines]
GO
/****** Object: ForeignKey [FK_Products_Providers] Script Date: 02/18/2013 12:41:39 ******/
ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [FK_Products_Providers] FOREIGN KEY([ProviderID])
REFERENCES [dbo].[Providers] ([ProviderID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Providers]
GO
/****** Object: ForeignKey [FK_Products_addons_Products] Script Date: 02/18/2013 12:41:39 ******/
ALTER TABLE [dbo].[Products_addons] WITH CHECK ADD CONSTRAINT [FK_Products_addons_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
GO
ALTER TABLE [dbo].[Products_addons] CHECK CONSTRAINT [FK_Products_addons_Products]
GO
/****** Object: ForeignKey [FK_Products_addons_Products1] Script Date: 02/18/2013 12:41:39 ******/
ALTER TABLE [dbo].[Products_addons] WITH CHECK ADD CONSTRAINT [FK_Products_addons_Products1] FOREIGN KEY([AddonProductID])
REFERENCES [dbo].[Products] ([ProductID])
GO
ALTER TABLE [dbo].[Products_addons] CHECK CONSTRAINT [FK_Products_addons_Products1]
GO
/****** Object: ForeignKey [FK_ProductsCharacteristics_CharacteristicLookup] Script Date: 02/18/2013 12:41:39 ******/
ALTER TABLE [dbo].[ProductsCharacteristics] WITH CHECK ADD CONSTRAINT [FK_ProductsCharacteristics_CharacteristicLookup] FOREIGN KEY([CharacteristicID])
REFERENCES [dbo].[CharacteristicLookup] ([CharacteristicID])
GO
ALTER TABLE [dbo].[ProductsCharacteristics] CHECK CONSTRAINT [FK_ProductsCharacteristics_CharacteristicLookup]
GO
/****** Object: ForeignKey [FK_ProductsCharacteristics_Products] Script Date: 02/18/2013 12:41:39 ******/
ALTER TABLE [dbo].[ProductsCharacteristics] WITH CHECK ADD CONSTRAINT [FK_ProductsCharacteristics_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ProductsCharacteristics] CHECK CONSTRAINT [FK_ProductsCharacteristics_Products]
GO
Go to Top of Page

ymamalis
Starting Member

42 Posts

Posted - 02/18/2013 :  05:43:35  Show Profile  Visit ymamalis's Homepage  Reply with Quote
Actually i need in the output both the product id and the subbroduct id if there is a subproduct with one of those characteristics
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  06:12:30  Show Profile  Reply with Quote
you mean a product with all subproducts as listed in CharacteristicLookup table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ymamalis
Starting Member

42 Posts

Posted - 02/18/2013 :  07:39:07  Show Profile  Visit ymamalis's Homepage  Reply with Quote
Yes , if i search fro products that have characteristics 1,5,6 then it should return one row with 1 and 5 as products id's if i look for characteristics 5, then it should return a row with 1,5 and a row with 6,0
Go to Top of Page

ymamalis
Starting Member

42 Posts

Posted - 02/18/2013 :  08:05:04  Show Profile  Visit ymamalis's Homepage  Reply with Quote
in my example the correct result should be row1=1,0 row2=1,5 , second example row1=6,0 , it should display the basic product with no addon (1,0) and the basic product with add-on (1,5) . i hope i exmplained to you properly
Go to Top of Page

ymamalis
Starting Member

42 Posts

Posted - 02/18/2013 :  10:00:51  Show Profile  Visit ymamalis's Homepage  Reply with Quote
i end it up with this query and i got the results i need. do you think there is another more clever way to achive the same result?
SELECT ProductID, AddonProductID
FROM (SELECT p.ProductID, p.AddonProductID, p.CharacteristicID
FROM (SELECT ProductID, CharacteristicID, 0 AS AddonProductID
FROM ProductsCharacteristics
UNION ALL
SELECT Products_addons.ProductID, ProductsCharacteristics_1.CharacteristicID, Products_addons.AddonProductID
FROM ProductsCharacteristics AS ProductsCharacteristics_1 LEFT OUTER JOIN
Products_addons ON ProductsCharacteristics_1.ProductID = Products_addons.AddonProductID
WHERE (Products_addons.ProductID IS NOT NULL)) AS p INNER JOIN
(SELECT CharacteristicID
FROM CharacteristicLookup
WHERE (CharacteristicID IN (1, 5, 6))) AS i ON i.CharacteristicID = p.CharacteristicID
GROUP BY p.ProductID, p.AddonProductID, p.CharacteristicID) AS r
GROUP BY ProductID, AddonProductID
HAVING (ProductID IN
(SELECT p_1.ProductID
FROM (SELECT ProductID, CharacteristicID
FROM ProductsCharacteristics AS ProductsCharacteristics_2
UNION
SELECT Products_addons_1.ProductID, ProductsCharacteristics_1.CharacteristicID
FROM ProductsCharacteristics AS ProductsCharacteristics_1 LEFT OUTER JOIN
Products_addons AS Products_addons_1 ON ProductsCharacteristics_1.ProductID = Products_addons_1.AddonProductID
WHERE (Products_addons_1.ProductID IS NOT NULL)) AS p_1 INNER JOIN
(SELECT CharacteristicID
FROM CharacteristicLookup AS CharacteristicLookup_2
WHERE (CharacteristicID IN (1, 5, 6))) AS i_1 ON i_1.CharacteristicID = p_1.CharacteristicID
GROUP BY p_1.ProductID
HAVING (COUNT(DISTINCT p_1.CharacteristicID) =
(SELECT COUNT(DISTINCT CharacteristicID) AS PropertyIDCount
FROM CharacteristicLookup AS CharacteristicLookup_1
WHERE (CharacteristicID IN (1, 5, 6))))))
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000