SQL Query Help

[Tal]

Alright. I have 2 tables:

User_X table
----------------
UserID (Int)
Name (Char)
Group_List (Char)

Group_X Table
------------------
GroupID (Int)
GroupName (Char)

I want to take every row in the Group_X table and check for the presence of the row's Group_ID field within the Group_List field of User_X.

IE: Group_List for a specific row(user) could read as 100 ,200 ,300 blah blah blah

So how do I, for every group, show the users that have membership in that group, as determined by the group's ID being present (anywhere) inside the Group_List field?
-------------------------------------------------------------------------------------------------
Thanks in advance! -Tal

 

[znaps]

Why is the DB designed like that? Can you change the structure? That's really ugly.

 

[Jeraden]

Assume your list is stored "100,200,300" and you want to match for 200. You can't just do like '%200%', as maybe there is a ,2000,3000 in there, where 2000 would match %200%.

So I'm converting commas to spaces, and putting a space at the beginning and end too, since there probably isn't one already. That way there is always a space at each side of the groupID. Then you can match with like '% 200 %' with the space and get the right number.

Hope that makes sense.

If your list is really 100,200,300 and not 100, 200, 300 (no extra spaces in there) you could just do
and ',' || ux.group_list || ',' like '%,' || gx.groupID || ',%'
instead, where you just tack an extra comma at the beginning and end, but the attached code would work better in case some spaces get in there somehow.

 

[Tal]

Can't redesign the code. It's actually a Remedy (ARS) database.

Jeraden, that would work, but I have a lot of groups and need to do this for every group.

 

[HeroOfPellinor]

select groupname, name
from user_x u, group_x g
where groupid in (grouplist)
order by groupname

But, ya, FUGLY db there.

 

[KLin]

Originally posted by: HeroOfPellinor
select groupname, name
from user_x u, group_x g
where groupid in (grouplist)
order by groupname

But, ya, FUGLY db there.

The IN() clause does not work in that fashion.


EDIT: To the OP, you're probably going to have to figure out a way to parse that group_list field out then make a separate record for user_X, and the groups they belong to.

 

[Gunslinger08]

This would probably be easier to do in a language like C# after pulling out the whole dataset. You could use string tokenizer on the grouplist field and it'd take about 1% as much thought and effort.

 

[Jeraden]

Originally posted by: Tal
Can't redesign the code. It's actually a Remedy (ARS) database.

Jeraden, that would work, but I have a lot of groups and need to do this for every group.

Why can't you use what I have for every group? Just leave the gx.GroupID = 1 out and it will go through all groups.

select gx.groupName, ux.UserID, ux.Name
from user_x ux, group_x gx
where ' ' || replace(ux.group_list,',', ' ') || ' ' like '% ' || gx.groupID || ' %'
order by gx.groupName, ux.Name

 

[HeroOfPellinor]

Originally posted by: KLin

Originally posted by: HeroOfPellinor
select groupname, name
from user_x u, group_x g
where groupid in (grouplist)
order by groupname

But, ya, FUGLY db there.

The IN() clause does not work in that fashion.

Sure it does. My hastily thrown together code won't do it, but you can most definitely use the in clause in that manner.

select groupname
from group_x
where groupid in (select grouplist from user_x where userid = 1)

will get you a list of all the group belonging to that user. Should be easy enough to use a variable and counter to script something to do it.

 

[KLin]

Originally posted by: HeroOfPellinor

Originally posted by: KLin

Originally posted by: HeroOfPellinor
select groupname, name
from user_x u, group_x g
where groupid in (grouplist)
order by groupname

But, ya, FUGLY db there.

The IN() clause does not work in that fashion.

Sure it does. My hastily thrown together code won't do it, but you can most definitely use the in clause in that manner.

select groupname
from group_x
where groupid in (select grouplist from user_x where userid = 1)

will get you a list of all the group belonging to that user. Should be easy enough to use a variable and counter to script something to do it.

Not when all of the groups for a specific user are stored in one record separated by a comma.

 

[HeroOfPellinor]

Originally posted by: KLin

Originally posted by: HeroOfPellinor

Originally posted by: KLin

Originally posted by: HeroOfPellinor
select groupname, name
from user_x u, group_x g
where groupid in (grouplist)
order by groupname

But, ya, FUGLY db there.

The IN() clause does not work in that fashion.

Sure it does. My hastily thrown together code won't do it, but you can most definitely use the in clause in that manner.

select groupname
from group_x
where groupid in (select grouplist from user_x where userid = 1)

will get you a list of all the group belonging to that user. Should be easy enough to use a variable and counter to script something to do it.

Not when all of the groups for a specific user are stored in one record separated by a comma.

You mean stored in one field, right. And, if so, yes you can.

 

[KLin]

Originally posted by: HeroOfPellinor

Originally posted by: KLin

Originally posted by: HeroOfPellinor

Originally posted by: KLin

Originally posted by: HeroOfPellinor
select groupname, name
from user_x u, group_x g
where groupid in (grouplist)
order by groupname

But, ya, FUGLY db there.

The IN() clause does not work in that fashion.

Sure it does. My hastily thrown together code won't do it, but you can most definitely use the in clause in that manner.

select groupname
from group_x
where groupid in (select grouplist from user_x where userid = 1)

will get you a list of all the group belonging to that user. Should be easy enough to use a variable and counter to script something to do it.

Not when all of the groups for a specific user are stored in one record separated by a comma.

You mean stored in one field, right. And, if so, yes you can.

IN Clause

It will not work if the data is stored like this:

userID|Name|Group_List
1|John Doe|100, 200, 300|
2|Jane Doe|100, 200|

The data needs to be stored like this:

userID|Name|Group_List
1|John Doe|100
1|John Doe|200
1|John Doe|300
2|Jane Doe|100
2|Jane Doe|200

 

[HeroOfPellinor]

Look, just try it. I'm doing exactly this with a very small part of one of our corporate sites.

It's lousy db design, but it is doable.

 

[MrChad]

Originally posted by: KLin

Originally posted by: HeroOfPellinor
You mean stored in one field, right. And, if so, yes you can.

IN Clause

It will not work if the data is stored like this:

userID|Name|Group_List
1|John Doe|100, 200, 300|
2|Jane Doe|100, 200|

The data needs to be stored like this:

userID|Name|Group_List
1|John Doe|100
1|John Doe|200
1|John Doe|300
2|Jane Doe|100
2|Jane Doe|200

KLin is right, Hero, your SQL will not work. You can accomplish what you're suggesting using some of Oracle's dynamic SQL functions, but that depends on what DBMS the OP is using.

 

[HeroOfPellinor]

What you guys are trying to tell me is that you can't use comma separated values, from one field, in an IN clause. And that's plain wrong. My code works....I copied and pasted that from a query I did on out own DB.

If he set up a counter variable with the record count from a query of the users and then decremented it down to zero, each time putting the results in a temp table, he could absolutely do what he's trying to do with just an IN clause inside a WHERE clause.

 

[MrChad]

Originally posted by: HeroOfPellinor
What you guys are trying to tell me is that you can't use comma separated values, from one field, in an IN clause. And that's plain wrong. My code works....I copied and pasted that from a query I did on out own DB.

If he set up a counter variable with the record count from a query of the users and then decremented it down to zero, each time putting the results in a temp table, he could absolutely do what he's trying to do with just an IN clause inside a WHERE clause.

What DBMS? I swear I've tried what you're describing before and it didn't work. Perhaps it only works for comma-separated lists of numeric values and not strings.

 

[znaps]

He's using LIKE, not IN. It'll work.

[edit] nevermind..it should work either way, but probably not on all DBs [/edit]

 

[Gunslinger08]

Assuming the values are stored the way you said, with a space after the value and before the comma, this code will work.

For me, it returns:
Fifth 1 Josh
First 1 Josh
Fourth 2 Sean
Second 1 Josh
Second 2 Sean
Third 2 Sean

 

[HeroOfPellinor]

Originally posted by: MrChad

Originally posted by: HeroOfPellinor
What you guys are trying to tell me is that you can't use comma separated values, from one field, in an IN clause. And that's plain wrong. My code works....I copied and pasted that from a query I did on out own DB.

If he set up a counter variable with the record count from a query of the users and then decremented it down to zero, each time putting the results in a temp table, he could absolutely do what he's trying to do with just an IN clause inside a WHERE clause.

What DBMS? I swear I've tried what you're describing before and it didn't work. Perhaps it only works for comma-separated lists of numeric values and not strings.

That's probably it. I've only done it with numbers. Strings would need to be in single quotes which is why that probably wouldn't work.

 

[Gunslinger08]

Originally posted by: HeroOfPellinor

Originally posted by: MrChad

Originally posted by: HeroOfPellinor
What you guys are trying to tell me is that you can't use comma separated values, from one field, in an IN clause. And that's plain wrong. My code works....I copied and pasted that from a query I did on out own DB.

If he set up a counter variable with the record count from a query of the users and then decremented it down to zero, each time putting the results in a temp table, he could absolutely do what he's trying to do with just an IN clause inside a WHERE clause.

What DBMS? I swear I've tried what you're describing before and it didn't work. Perhaps it only works for comma-separated lists of numeric values and not strings.

That's probably it. I've only done it with numbers. Strings would need to be in single quotes which is why that probably wouldn't work.

Nope, doesn't work with strings with ticks.

 

[statik213]

Wouldn't a LEFT INNER JOIN work here?

SELECT GroupID, GroupName, UserID, Name
FROM group_x
LEFT JOIN Group_X ON (cond)
ORDER BY whatever

cond should probably be a regex, something like:
(Group_List REGEXP CONCAT('.*[^\d], GroupID, '[^\d].*)

OK, the regex up there may be greediesh, use a better pattern if you can. not sure if it is syntatically correct either, but get the idea?

 

[statik213]

^^ OK use IN instead of REGEXP

 

[icelazer]

Just perusing around here, but one idea could be to create a temporary table that looks something like

user 1 group 1
user 1 group 2
user 2 group 2
user 3 group 1
...

by parsing the group list field and then use those results for your overall query...using nested queries.

 

[Tal]

I tried doing it as mentioned above....

But got an error right after the first ' after the where saying invalid number. What a hassle this is.

 

[Jeraden]

What was wrong with the query in my 2nd post? Its doing the same thing what you just tried is doing, but without having to have 3 OR clauses.