Problem in String Compare
-
hi my table is like ProductID CagtegoryID 1 1,2, 2 1, 3 4, 4 2,3, and for SELECT Query if i give the categoryid=1,2 then i want the output like ProductID CagtegoryID 1 1,2, 2 1, 4 2,3,
Jintal Patel
-
hi my table is like ProductID CagtegoryID 1 1,2, 2 1, 3 4, 4 2,3, and for SELECT Query if i give the categoryid=1,2 then i want the output like ProductID CagtegoryID 1 1,2, 2 1, 4 2,3,
Jintal Patel
I think you need to learn about normalisation. You are storing multiple things in the categoryID column. By the looks of it you have a many to many join. To create a many-to-many join you need an intermediate table. So you have a Product table and a Category table and in between you have a ProductCategory table. The intermediate ProductCategory table contains just the primary keys of the source tables. So, Product table: ProductID 1 2 3 4 Category table: CategoryID 1 2 3 4 ProductCategory table: ProductID CategoryID 1 1 1 2 2 1 3 4 4 2 4 3 To get the results you want:
SELECT ProductID, CategoryID FROM ProductCategory WHERE CategoryID IN (1,2)
If you need other columns from the product and category tables you'll need a join too. e.g.
SELECT p.ProductID, c.CategoryID, p.ProductName, c.CategoryName
FROM Product AS p
INNER JOIN ProductCategory AS pc ON p.ProductID = pc.ProductID
INNER JOIN Category AS c on c.CategoryID = pc.CategoryID
WHERE c.CategoryID IN (1,2)
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
-
hi my table is like ProductID CagtegoryID 1 1,2, 2 1, 3 4, 4 2,3, and for SELECT Query if i give the categoryid=1,2 then i want the output like ProductID CagtegoryID 1 1,2, 2 1, 4 2,3,
Jintal Patel
creat a temporary table declare @temp table ( CagtegoryID varchar(100) ) insert the CagtegoryIDs into this table one by one it will be like CagtegoryID %,1,% %,2,% then try this statement select * from your table [your table ] A where exists (select 1 from @temp B where ','+A.CagtegoryID like B.CagtegoryID
-
I think you need to learn about normalisation. You are storing multiple things in the categoryID column. By the looks of it you have a many to many join. To create a many-to-many join you need an intermediate table. So you have a Product table and a Category table and in between you have a ProductCategory table. The intermediate ProductCategory table contains just the primary keys of the source tables. So, Product table: ProductID 1 2 3 4 Category table: CategoryID 1 2 3 4 ProductCategory table: ProductID CategoryID 1 1 1 2 2 1 3 4 4 2 4 3 To get the results you want:
SELECT ProductID, CategoryID FROM ProductCategory WHERE CategoryID IN (1,2)
If you need other columns from the product and category tables you'll need a join too. e.g.
SELECT p.ProductID, c.CategoryID, p.ProductName, c.CategoryName
FROM Product AS p
INNER JOIN ProductCategory AS pc ON p.ProductID = pc.ProductID
INNER JOIN Category AS c on c.CategoryID = pc.CategoryID
WHERE c.CategoryID IN (1,2)
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
thanks for Solution i getting my value by Like this SELECT * FROM [DB_Ecommerce].[dbo].[tb_Ecomm_Product] Where Charindex('8',CategoryId)<>0 or Charindex('2',CategoryId)<>0 ;
Jintal Patel
-
thanks for Solution i getting my value by Like this SELECT * FROM [DB_Ecommerce].[dbo].[tb_Ecomm_Product] Where Charindex('8',CategoryId)<>0 or Charindex('2',CategoryId)<>0 ;
Jintal Patel
Jintal Patel wrote:
SELECT * FROM [DB_Ecommerce].[dbo].[tb_Ecomm_Product] Where Charindex('8',CategoryId)<>0 or Charindex('2',CategoryId)<>0 ;
That is hardly a good solution in the face of properly modelling your data.
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
-
creat a temporary table declare @temp table ( CagtegoryID varchar(100) ) insert the CagtegoryIDs into this table one by one it will be like CagtegoryID %,1,% %,2,% then try this statement select * from your table [your table ] A where exists (select 1 from @temp B where ','+A.CagtegoryID like B.CagtegoryID
That has to be the craziest thing I've ever seen and it won't even return the correct answer. It does not handle cases where the category is the first or last on a list. There fore it there is only one or two categories it won't return anything. If there are three or more categories it will only return those in the middle.
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
-
That has to be the craziest thing I've ever seen and it won't even return the correct answer. It does not handle cases where the category is the first or last on a list. There fore it there is only one or two categories it won't return anything. If there are three or more categories it will only return those in the middle.
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
take it carefuly Patel's table: ProductID CagtegoryID 1 1,2, 2 1, 3 4, 4 2,3, search condition : categoryid=1,2 i transform it to : CagtegoryID %,1,% %,2,% my statement was
"select * from [your table ] A where exists (select 1 from @temp B where','+A.CagtegoryID like B.CagtegoryID )"the "','+" can handle the leading first Cagtegory. and the data in Patel's table "1,2**,",the last ",**" can handle the last one -
take it carefuly Patel's table: ProductID CagtegoryID 1 1,2, 2 1, 3 4, 4 2,3, search condition : categoryid=1,2 i transform it to : CagtegoryID %,1,% %,2,% my statement was
"select * from [your table ] A where exists (select 1 from @temp B where','+A.CagtegoryID like B.CagtegoryID )"the "','+" can handle the leading first Cagtegory. and the data in Patel's table "1,2**,",the last ",**" can handle the last oneOkay - I retract my statement that it won't return the correct data. However it is still a poor solution. The data should have been properly normalised for this query to work efficiently. This solution can't be optimised by the query optimiser and will always run slowly. That might be fine for a handful of rows but it will take a significant amount of time if the tables grow.
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
-
Okay - I retract my statement that it won't return the correct data. However it is still a poor solution. The data should have been properly normalised for this query to work efficiently. This solution can't be optimised by the query optimiser and will always run slowly. That might be fine for a handful of rows but it will take a significant amount of time if the tables grow.
Upcoming FREE developer events: * Glasgow: db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website