May 27, 2009 at 10:53 pm
Dear friends,
This is my table tblGroups
intGroupId intUserId
1 1,2,3
2 1,3
3 2,3,4
In this table i need to get intGroupId that where intUserId=2. That is "select intGroupId from tblGroups where intUserId=2."
This query is possible to get intGroupId from the above table. I need this one. What query is possible to get the id.
Hope yours reply.
Thanks
Ashok
May 28, 2009 at 12:41 am
HI Ashok,
suppose your table (Tblgroup)column intUserId having the datatype of char or varchar
then use this like select intGroupId from tblGroups where intUserId= '%2%'
to get the Result of
intGroupId
1
3
ARUN SAS
May 28, 2009 at 12:52 am
Hi dude thanks for your reply.
Yes its correct i got it. But query is this one that is WHERE intUserID LIKE '%2%'.
ou know any best schema for this condition that is Single users in multiple groups .Is it
affects performance??
Can you tell that without affect the perfomance we can do this dude.
Thanks
Ashok
May 28, 2009 at 12:58 am
Instead of storing the users in a coma separated string, you should use a table to describe the connection between users and there groups (you can Google for “many to many relationship” to learn more about it).
Having said that you can find the groups for users by using wildcards in the query. Notice that this will results in a table scan. The query should look like this (untested because you didn’t write a small script that creates the table and inserts data into it):
select intGroupId from tblGroups where intUserId like ‘%,2,%’ or intUserId like ‘2,%’ or intUserId like ‘%,2’
Adi
edited - Deleted an extra wild card at the end of the where clause
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 28, 2009 at 1:26 am
Hi Adi,
The scan is good,
but is any difference while putting the %2% instead of '%,2%' or '%,2,%'
ARUN SAS
May 28, 2009 at 2:41 am
arun.sas (5/28/2009)
Hi Adi,
The scan is good,
but is any difference while putting the %2% instead of '%,2%' or '%,2,%'
ARUN SAS
Yes there is. If you have user that his/her ID has the digit 2 somewhere in the column IntUserID (for example 23, 12, 102 etc’) then you’ll get that user if you’ll use the where Intuserid like ‘%2%’. On the other hand if you’ll use the where clause where intUserId like ‘%,2,%’ or intUserId like ‘2,%’ or intUserId like ‘%,2’, you’ll get only users with intUser that is exactly 2.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 28, 2009 at 3:07 am
Adi Cohn (5/28/2009)
arun.sas (5/28/2009)
Hi Adi,
The scan is good,
but is any difference while putting the %2% instead of '%,2%' or '%,2,%'
ARUN SAS
Yes there is. If you have user that his/her ID has the digit 2 somewhere in the column IntUserID (for example 23, 12, 102 etc’) then you’ll get that user if you’ll use the where Intuserid like ‘%2%’. On the other hand if you’ll use the where clause where intUserId like ‘%,2,%’ or intUserId like ‘2,%’ or intUserId like ‘%,2’, you’ll get only users with intUser that is exactly 2.
Adi
Yes! correct Adi,
OP, use this statement
declare @abc TABLE
(
slno int,
Filter varchar(50)
)
insert into @abc values (1,'23,12,102')
insert into @abc values (2,'1,2,111,222,333,444,555')
insert into @abc values (3,'1,3,111,222,333,444,555')
declare @param int
set @param = 2
select SLNO from @abc
where Filter like '%,'+cast(@param as varchar(5))+',%'
or Filter like '%,'+cast(@param as varchar(5))
or Filter like cast(@param as varchar(5))+',%'
ARUN SAS
May 28, 2009 at 9:59 am
Hi,
Here is a solution based upon tally table ( http://www.sqlservercentral.com/articles/T-SQL/62867/%5B/url) for normalising the structure of @abc.
First of all set up tally table
--=============================================================================
-- Setup
--=============================================================================
USE TempDB --DB that everyone has where we can cause no harm
SET NOCOUNT ON --Supress the auto-display of rowcounts for appearance/speed
DECLARE @StartTime DATETIME --Timer to measure total duration
SET @StartTime = GETDATE() --Start the timer
--=============================================================================
-- Create and populate a Tally table
--=============================================================================
--===== Conditionally drop
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Let the public use it
GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC
--===== Display the total duration
SELECT STR(DATEDIFF(ms,@StartTime,GETDATE())) + ' Milliseconds duration'
Have amended code from article to split un-normal column on comma:
declare @abc TABLE
(
slno int,
Filter varchar(50)
)
insert into @abc values (1,'23,12,102')
insert into @abc values (2,'1,2,111,222,333,444,555')
insert into @abc values (3,'1,3,111,222,333,444,555')
select * from @abc
declare @NormalABC TABLE
(
slno INT,
NormalFilter varchar(50)
)
insert into @NormalABC
select
slno,
substring(',' + Filter + ',', N + 1, charindex(',', ',' + Filter + ',', N + 1) - N - 1)
from @abc, Tally
where
N < len(',' + Filter + ',')
and substring(',' + Filter + ',', N, 1) = ','
select * from @NormalABC where NormalFilter = '2'
With a normalised table scan will be a lot faster, adding index would improve performance further.
Allister
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply