Please Help to write Query...

[happyIndian100]

Hi Every one,
can any one help me to write this query. I have a Database table called "User" and it have UserID(Int(4)) and Roles(varchar(50)) Feilds.
Sample data is like this:

UserID Roles
----------------------------------
A 2
B 3
C 2,3,4
D 3,4
E NULL
F NULL
G 5
H 6,7


I need to get all the records whose Roles are 2,3,4
i.e., i need to get UserIDs (A,B,C,D) with the query.

Please Help....

 

[happyIndian100]

I got it.

This will be the query..
SELECT UID FROM Users WHERE (Roles LIKE '%2%') OR
(Roles LIKE '3%') OR (Roles LIKE '%4%')

please let me know if there is more efficient way of quering....

 

[Steve101]

You can abbreviate the SQL a little as follows:
[tt]
SELECT UID FROM Users
WHERE (Roles LIKE '%[234]%'
[/tt]
The square brackets above will essentially match eithor 2 or 3 or 4; if the range was greater; eg 2 to 7, you could use [2-7] as a shorter alternative to [234567].


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[happyIndian100]

Thankx steve

but what if the Roles is in 2 digit also like 1,2,3,4,10,11,12....

 

[Golom]

Then (plagerizing from Steve101)
[tt]
WHERE Roles LIKE '%[1-4]%' OR Roles LIKE '%[10-12]%'
[/tt]

 

[happyIndian100]

i want to get records where roles like 2,10,12,14

 

[tektipdjango]

You have a problem with your database design.
For the values you want it's still mangeable with happyIndian's solution (like '%12%' or...) but it's because the values you want are selectable deferently.
But imagine you want 2,10,14 without selecting 12.
You will have to make a lot of tests
like '%2%' but not like '%12%'...

In fact the design would be Id,Role (role without's' as there would be one record for each role)

UserID Role
----------------------------------
A 2
B 3
[highlight]C 2
C 3
C 4[/highlight]
D 3
D 4
E NULL
F NULL
G 5
[highlight]H 6
H 7[/highlight]

then the query will be much simpler as it will always of the same form.
You will be able also to index the role field speeding up the query

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​