SQLTeam.com | Weblogs | Forums

Complex query help from single table


#1

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

#2
select productid from table
where name = '60 months'
intersect
select productid from table
where name = 'tv'

#3

60 Months AND TV
Pls note, it should satisfy AND condition.. thanks


#4

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.

Is that not what you are seeing?


#5

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


#6

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

#7

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)

#8

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.


#9

Thanks.. Really sorry my bad communication, here parameter means, sending dynamically values of like
samsung, TV, 60Months, LG,etc


#10

hi Scoot, have you seen my reply?.. your help is really appreciated.. thanks


#11

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 .


#12

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..

thanks


#13

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)


#14

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...