Hi friends,
I have a small problem.
I have a login table in which there are usernames and the group which he belongs to. A user can belong to a single group and also to many groups.
Eg:
Mr. X----- Group1, Group2
Mr. Y----- Group1, Group2, Group7
Mr. Z----- Group3, Group7
and so on...
These group names are stored in a single field seperated by a comma
Now i need to select names of people who belong to Group 7. Whats the query we write for this?
Another question parellely. There are many system which belong to a group. I have set a session variable in the login of the user session("MC")=rs.fields(2).value
where rs.fields(2).value is the group names seperated by comma.
Based on the group names of the user i need to select some records from machine table.
M/c-1 -------------- Group-1
M/c-2 -------------- Group-7
and so on...
I hope i am clear in my question. Please help.
regards
Karthik.RHi friends,
I have a small problem.
I have a login table in which there are usernames and the group which he belongs to. A user can belong to a single group and also to many groups.
Eg:
Mr. X----- Group1, Group2
Mr. Y----- Group1, Group2, Group7
Mr. Z----- Group3, Group7
and so on...
These group names are stored in a single field seperated by a comma
Now i need to select names of people who belong to Group 7. Whats the query we write for this?
Another question parellely. There are many system which belong to a group. I have set a session variable in the login of the user session("MC")=rs.fields(2).value
where rs.fields(2).value is the group names seperated by comma.
Based on the group names of the user i need to select some records from machine table.
M/c-1 -------------- Group-1
M/c-2 -------------- Group-7
and so on...
I hope i am clear in my question. Please help.
regards
Karthik.R
You need a cross reference table.
UserTable
----------
UserId
UserName
Group Table
---------
GroupId
GroupName
User Group X Ref
-----------
GroupId
UserId
now the query would be
select * from tblUsers where UserId in (Select UserId from tblUserGroupXRef where GroupId = @GroupId)
where "@GroupId" is your Group identifier that you are looking at.you could also use the LIKE operator, ie. select * for table where group LIKE "%7" or something like that...not sure on the syntax, but its similarthe like operator is for strings. That is completely wrong. THe in statement was correct.the like operator is for strings. That is completely wrong. THe in statement was correct.
Hmm, in Kram's defence I must say that I would have used the LIKE operator too:
SQL = "SELECT * FROM login WHERE GroupID LIKE '%Group7%'"
But I agree that having a cross-reference table would be better though, the way the groups are currently being stored isn't right at all. But then what would I know!
Ok defend him all you like but it is wrong, Check with SQL Team or a few other websites they will agree.
DO you know that SQL has to convert it implicitly to the datatype that it is being compared against?the single qoutes in the like part of the statement make the type of data for the compare a string..... SQL has to assume you knew what you were doing by making it a string. So to compare it, the field will be converted.Fair enough, you know a lot more about this than me I am sure!Heys Guys,
Thanks for all your suggestions. But I dont think so any of them answered my question
.
I actually want to select records based on group id / multiple group ids.
Syntax something like this.
select * from table where gid=1,2,4,8
Hope you get my point.
Any suggestions?
Thanks once again..
Karthik.RI did answer it.... LOL
the "in" statement
select * from table where id in (1,3,4,5,9)the like operator is for strings. That is completely wrong. THe in statement was correct.
i thought it was strings, he said his groups were called group1, group2 etc... so i just assumedYeah....if that is actually the name of the group (Group7) then you could use the Like but then you could possibly get Group7,Group17,Group27....etc.
If you know the actual name of the group, then it is best to use that exact name and not do the Like for that reason.
In Burn's case, it's assumed that the ID number for the Group has been sent into the query rather than the name and, in that case, what he has is the right answer.
All depends on what is being referenced......ID Number or Group Name.Yeah, i know. Its tricky to decide some help sometimes when you have to assume things. But i would assume that Burn's stuff sounds the best. thats was just the first thing that poped in my headHi,
We did similar to this at work while back. We used functions to pull up the (CSV comma delimited) format.
Here is the code we use:
Function Report_Title()
sngPageCount = sngPageCount + 1
If Option3.Value = True Then
Print "Orlando Epting - "; strMo & " " & strDay & ", "; strYear
Print ""
Print Spc(30); "PROPOSED BOND ISSUES"; Spc(17); "Page:"; Spc(1); sngPageCount
Print ""
Print Spc(2); "DISTRICT"; Spc(3); "COUNTY"; Spc(3); "CITY"; Spc(5); _
"ACCOUNT"; Spc(19); "BOND"
Print Spc(2); "NUMBER"; Spc(5); "NUMBER"; Spc(3); "NUMBER"; Spc(3); _
"NUMBER"; Spc(4); "NAME"; Spc(22); "AMOUNT"
Print String(80, "_")
Print ""
ElseIf Option4.Value = True Then
Printer.Print "Orlando Epting - "; strMo & " " & strDay & ", "; strYear
Printer.Print ""
Printer.FontBold = True
Printer.Print Spc(30); "PROPOSED BOND ISSUES"; Spc(17); "Page:"; Spc(1); sngPageCount
Printer.Print ""
Printer.Print Spc(2); "DISTRICT"; Spc(3); "COUNTY"; Spc(3); "CITY"; Spc(5); _
"ACCOUNT"; Spc(19); "BOND"
Printer.Print Spc(2); "NUMBER"; Spc(5); "NUMBER"; Spc(3); "NUMBER"; Spc(3); _
"NUMBER"; Spc(4); "NAME"; Spc(22); "AMOUNT"
Printer.Print String(80, "_")
Printer.FontBold = False
Printer.Print ""
End Function
Dexter Zafra
<!-- w --><a class="postlink" href="http://www.ex-designz.net">www.ex-designz.net</a><!-- w -->
I have a small problem.
I have a login table in which there are usernames and the group which he belongs to. A user can belong to a single group and also to many groups.
Eg:
Mr. X----- Group1, Group2
Mr. Y----- Group1, Group2, Group7
Mr. Z----- Group3, Group7
and so on...
These group names are stored in a single field seperated by a comma
Now i need to select names of people who belong to Group 7. Whats the query we write for this?
Another question parellely. There are many system which belong to a group. I have set a session variable in the login of the user session("MC")=rs.fields(2).value
where rs.fields(2).value is the group names seperated by comma.
Based on the group names of the user i need to select some records from machine table.
M/c-1 -------------- Group-1
M/c-2 -------------- Group-7
and so on...
I hope i am clear in my question. Please help.
regards
Karthik.RHi friends,
I have a small problem.
I have a login table in which there are usernames and the group which he belongs to. A user can belong to a single group and also to many groups.
Eg:
Mr. X----- Group1, Group2
Mr. Y----- Group1, Group2, Group7
Mr. Z----- Group3, Group7
and so on...
These group names are stored in a single field seperated by a comma
Now i need to select names of people who belong to Group 7. Whats the query we write for this?
Another question parellely. There are many system which belong to a group. I have set a session variable in the login of the user session("MC")=rs.fields(2).value
where rs.fields(2).value is the group names seperated by comma.
Based on the group names of the user i need to select some records from machine table.
M/c-1 -------------- Group-1
M/c-2 -------------- Group-7
and so on...
I hope i am clear in my question. Please help.
regards
Karthik.R
You need a cross reference table.
UserTable
----------
UserId
UserName
Group Table
---------
GroupId
GroupName
User Group X Ref
-----------
GroupId
UserId
now the query would be
select * from tblUsers where UserId in (Select UserId from tblUserGroupXRef where GroupId = @GroupId)
where "@GroupId" is your Group identifier that you are looking at.you could also use the LIKE operator, ie. select * for table where group LIKE "%7" or something like that...not sure on the syntax, but its similarthe like operator is for strings. That is completely wrong. THe in statement was correct.the like operator is for strings. That is completely wrong. THe in statement was correct.
Hmm, in Kram's defence I must say that I would have used the LIKE operator too:
SQL = "SELECT * FROM login WHERE GroupID LIKE '%Group7%'"
But I agree that having a cross-reference table would be better though, the way the groups are currently being stored isn't right at all. But then what would I know!
Ok defend him all you like but it is wrong, Check with SQL Team or a few other websites they will agree.DO you know that SQL has to convert it implicitly to the datatype that it is being compared against?the single qoutes in the like part of the statement make the type of data for the compare a string..... SQL has to assume you knew what you were doing by making it a string. So to compare it, the field will be converted.Fair enough, you know a lot more about this than me I am sure!
Heys Guys,Thanks for all your suggestions. But I dont think so any of them answered my question
. I actually want to select records based on group id / multiple group ids.
Syntax something like this.
select * from table where gid=1,2,4,8
Hope you get my point.
Any suggestions?
Thanks once again..
Karthik.RI did answer it.... LOL
the "in" statement
select * from table where id in (1,3,4,5,9)the like operator is for strings. That is completely wrong. THe in statement was correct.
i thought it was strings, he said his groups were called group1, group2 etc... so i just assumedYeah....if that is actually the name of the group (Group7) then you could use the Like but then you could possibly get Group7,Group17,Group27....etc.
If you know the actual name of the group, then it is best to use that exact name and not do the Like for that reason.
In Burn's case, it's assumed that the ID number for the Group has been sent into the query rather than the name and, in that case, what he has is the right answer.
All depends on what is being referenced......ID Number or Group Name.Yeah, i know. Its tricky to decide some help sometimes when you have to assume things. But i would assume that Burn's stuff sounds the best. thats was just the first thing that poped in my headHi,
We did similar to this at work while back. We used functions to pull up the (CSV comma delimited) format.
Here is the code we use:
Function Report_Title()
sngPageCount = sngPageCount + 1
If Option3.Value = True Then
Print "Orlando Epting - "; strMo & " " & strDay & ", "; strYear
Print ""
Print Spc(30); "PROPOSED BOND ISSUES"; Spc(17); "Page:"; Spc(1); sngPageCount
Print ""
Print Spc(2); "DISTRICT"; Spc(3); "COUNTY"; Spc(3); "CITY"; Spc(5); _
"ACCOUNT"; Spc(19); "BOND"
Print Spc(2); "NUMBER"; Spc(5); "NUMBER"; Spc(3); "NUMBER"; Spc(3); _
"NUMBER"; Spc(4); "NAME"; Spc(22); "AMOUNT"
Print String(80, "_")
Print ""
ElseIf Option4.Value = True Then
Printer.Print "Orlando Epting - "; strMo & " " & strDay & ", "; strYear
Printer.Print ""
Printer.FontBold = True
Printer.Print Spc(30); "PROPOSED BOND ISSUES"; Spc(17); "Page:"; Spc(1); sngPageCount
Printer.Print ""
Printer.Print Spc(2); "DISTRICT"; Spc(3); "COUNTY"; Spc(3); "CITY"; Spc(5); _
"ACCOUNT"; Spc(19); "BOND"
Printer.Print Spc(2); "NUMBER"; Spc(5); "NUMBER"; Spc(3); "NUMBER"; Spc(3); _
"NUMBER"; Spc(4); "NAME"; Spc(22); "AMOUNT"
Printer.Print String(80, "_")
Printer.FontBold = False
Printer.Print ""
End Function
Dexter Zafra
<!-- w --><a class="postlink" href="http://www.ex-designz.net">www.ex-designz.net</a><!-- w -->