hi i have table called attritbutes. in this table, i want to get the product id of those, whos value
are 60 Months AND TV from the Name field
** Name AName productid id**
Levis Brand 76294 406
T shirt Type 76294 407
60 Months warranty 76294 409
Samsung Brand 76301 410
TV Type 76301 411
60 Months warranty 76294 412
LG Brand 76295 413
TV Type 76295 414
60 Months warranty 76295 415
It seems to me like the query @gbritton posted should exactly give you that - namely all productId's which satisfy two conditions: a) there is a row with name = '60 months' for that product Id AND b) there is a row with name = 'TV' for that productid.
im sorry, wht im looking,
wht ever the productid comes from TV
should exits in the productid comes from 60Months
and I will add in the future "Samsung"
So that means, the values of the productid should be there (Match) in all three values of
(samsung, TV, 60Months)
the below query works fine
select productid from new_table1 where Name='samsung' and productid in
(select productid from new_table1 where Name='TV' and productid in
(select productid from new_table1 where Name='60Month'))
but my where condition is dynamic, the parameters values dynamic, the sending parameters can be 1, 2,3, 4,5,etc..
and so i want to call dynamic query or stored procedure for asp.net
either build the query in ASP.net or create a simple proc using the query I posted, with two input variables: one for each condition. Then run the select with those vars.
create proc foo(@one varchar(50), @two varchar(50) as
begin
set nocount on
select productid from table
where name = @one
intersect
select productid from table
where name = @two
end
Since there are a variable number of values, easiest is to create a temp table with them in it, then use that in the main query. That way the main code doesn't change, only what gets loaded into the controlling table.
The code below is if all values must match. For other matches -- such as 2 of 3 or 3 of 4 -- the HAVING statement needs changed, that is all.
IF OBJECT_ID('tempdb.dbo.#names') IS NOT NULL
DROP TABLE #names
--insure the temp table, with the list of values, has exactly the same data type as the main table
SELECT TOP (1) name
INTO #names
FROM dbo.attributes
WHERE 1 = 0
INSERT INTO #names VALUES('60 Months'), ('Samsung'), ('TV') --...
SELECT a.productid
FROM dbo.attributes a
WHERE a.Name IN (SELECT n.name FROM #names n)
GROUP BY a.productid
HAVING COUNT(*) = (SELECT COUNT(*) FROM #names)
Please follow basic Netiquette and post the DDL we need to answer this. Follow Industry and ANSI/ISO standards In your data. You should follow ISO-11179 rules for naming data elements.
We need to know the data types, keys and constraints on the table. Avoid dialect In favor of ANSI/ISO Standard SQL.
I have the below table
NO! This Is just a picture and very rude. We have to guess at the DDL you did not even try to post. Then we have to type It for you. We do not even have a valid table name!
Columns are not fields. This difference is covered in the first week of a good SQL class or any book.
have table called attributes.
NO! That term is meta data and we never mix data and meta-data in a table. Are you trying to write a NoSQL (attribute, value) pair database in SQL? This does not work. There is no such crap as a generic "id" in RDBMS; that is the kabbalah number from magic, not an identifier from logic.
Did you know that the UPC or EAN or GTIN bar codes have the brand and product names in them? You could normalize this schema. Here is my guess at repairs:
CREATE TABLE Products
(brand_name VARCHAR(20) NOT NULL,
product_name VARCHAR(20) NOT NULL,
sku CHAR (5) NOT NULL PRIMARY KEY,
warranty_duration INTEGER DEFAULT 0 NOT NULL
CHECK (warranty_duration >= 0)
);
In this table, I want to get the sku of those, whose value
are 60 Months and TV from the Name [sic] field [sic] <<
SELECT sku, brand_name, product_name, warranty_duration
FROM Products
WHERE warranty_duration >= 60
AND product_name = 'TV';
You do not have a complicated query; you have a bad design that makes all queries needlessly difficult.
Doesn't my code handle that? You can load the dynamically passed values into either a table variable or a temp table and process against that. The advantage of that technique is that the main query doesn't have to be changed .
thanks the results are empty.. this query works fine
select productid from new_table1 where Name='samsung' and productid in
(select productid from new_table1 where Name='TV' and productid in
(select productid from new_table1 where Name='60Month'))
but it needs to be dynamically passing more values, each value like each subquery..here..
The same name must be able to appear multiple times in the table then. So change the final query to:
SELECT a.productid
FROM dbo.attributes a
WHERE a.Name IN (SELECT n.name FROM #names n)
GROUP BY a.productid
HAVING COUNT(DISTINCT a.Name) = (SELECT COUNT(*) FROM #names)
This works excellent, and thanks for your Support, which works fine and display all the related product attributes of my filter (26ah)
IF OBJECT_ID('tempdb.dbo.#names') IS NOT NULL DROP TABLE #names SELECT
TOP (1) name INTO #names FROM dbo.view_newtable WHERE 1 = 0 INSERT
INTO #names VALUES ('26ah')
SELECT
Distinct(SpecificationAttributeOption.Name), SpecificationAttribute.name as AName,
SpecificationAttributeOptionId, SpecificationAttribute.id,
SpecificationAttributeOption.SpecificationAttributeId,Product_SpecificationAttribute_Mapping.DisplayOrder
FROM SpecificationAttributeOption INNER JOIN
Product_SpecificationAttribute_Mapping ON SpecificationAttributeOption.Id =
Product_SpecificationAttribute_Mapping.SpecificationAttributeOptionId
INNER JOIN SpecificationAttribute on
SpecificationAttributeOption.specificationattributeid = SpecificationAttribute.id WHERE
(Product_SpecificationAttribute_Mapping.AllowFiltering = 1) AND
(Product_SpecificationAttribute_Mapping.ShowOnProductPage = 1) AND
Product_SpecificationAttribute_Mapping.productid in ( SELECT a.productid
FROM dbo.view_newtable a WHERE a.categoryName='battery' AND a.Name IN
(SELECT n.name FROM #names n) GROUP BY a.productid HAVING COUNT(DISTINCT a.Name) = (SELECT COUNT(*) FROM #names) )
I need to have productid COUNT besides the above output
if I keep Product_SpecificationAttribute_Mapping.Productid, in the main query, it give me lot of duplicate records and im confused.. Appreciate if you can help me on this as last request for this topic... Thanks a lot...