Hard SQL/Fill list Question

MichaelTJ · October 26th, 2003, 08:43 PM

Hi all,
I've been working on this brain eater for a looong time now.
And I need some help here!
I have one Table named GroupMembers this holds data to show
what members are in what groups(GroupName, GroupLevel and MobileNr).
Eks. Games, Level2, 90010000

I have another table with hirarcy data for the groups (Level1, Level2, Level3, Level4)
Eks1: Groups, Games, NULL, NULL
Eks2: Whole Country, South, Brattish, Sompin

I have 4 ListBox, The first always Contains 'Whole Country' and 'Groups' when one of
these are clicked the next listbox appeare with the (Level2) data from SQL and so on.
This works fine.
What I need now is that all the entries under 'Whole Country' to always appeare
if 'Whole Country' is selected, but only the entries under groups whitch the member
is registered to appeare if 'Groups' is selected.

**************************CODE TO POPULATE LIST FROM SQL******************
Sub FillData(ByVal Level As Int32)
Session.Item("Level") = Level
Session.Item("CSelection") = ListColl(Level).SelectedValue
Level = Level + 1
Select Case Level
  Case 1
    FillList(Level, "Select Distinct Level1 from SelectGroup")
  Case 2
    FillList(Level, "Select Distinct Level2 from SelectGroup where level1 = '" & Session.Item("CSelection") & "'")
  Case 3
    FillList(Level, "Select Distinct Level3 from SelectGroup where level2 = '" & Session.Item("CSelection") & "'")
  Case 4
    FillList(Level, "Select Distinct Level4 from SelectGroup where level3 = '" & Session.Item("CSelection") & "'")
End Select
CheckLists(Level)
Session.Item("LevelSelected") = Level
TextBox1.Text = Session.Item("CSelection")
End Sub
***************************END CODE******************************

*****************SQL QUERRY WORKING TO GET ONLY GROUPS***********
Select Distinct SelectGroup.Level2 From SelectGroup
Join(GroupMembers)
ON (GroupMembers.GroupName = SelectGroup.Level2)
where GroupMembers.MobilNr = '90010000'

(If more info is needed Please let me know)

If anybody have some pointers on how to use Inner, Outer, Left Right Join (I belive my solution is there)
I will be most grateful..

------------------------
All help is Good help!
Regards
Michael

MichaelTJ · October 27th, 2003, 12:52 PM

I was up all night trying to figure out a way to do this.. then it hit me. Why not incorperate a if then with the SQL string.. he he he

****************WORKING CODE***************
Sub FillData(ByVal Level As Int32)
        Session.Item("Level") = Level
        Session.Item("CSelection") = ListColl(Level).SelectedValue
        Level = Level + 1
        Select Case Level
            Case 1
                FillList(Level, "Select Distinct Level1 from SelectGroup")
            Case 2
                If Session.Item("Level1") = "Groups" Then
                    FillList(Level, "Select Distinct Level2 from SelectGroup where level1 = '" & Session.Item("CSelection") & "'")
                Else
                    FillList(Level, "Select Distinct SelectGroup.Level2 From SelectGroup Join GroupMembers ON (GroupMembers.GroupName = SelectGroup.Level2) where GroupMembers.MobilNr = '" & Session.Item("UMobileNR") & "' AND SelectGroup.Level1 = '" & Session.Item("CSelection") & "'")
                End If
            Case 3
                If Session.Item("Level1") = "Whole Country" Then
                    FillList(Level, "Select Distinct Level3 from SelectGroup where level2 = '" & Session.Item("CSelection") & "'")
                Else
                    FillList(Level, "Select Distinct SelectGroup.Level3 From SelectGroup Join GroupMembers ON (GroupMembers.GroupName = SelectGroup.Level3) where GroupMembers.MobilNr = '" & Session.Item("UMobileNR") & "' AND SelectGroup.Level2 = '" & Session.Item("CSelection") & "'")
                End If
            Case 4
                If Session.Item("Level1") = "Whole Country" Then
                    FillList(Level, "Select Distinct Level4 from SelectGroup where level3 = '" & Session.Item("CSelection") & "'")
                Else
                    FillList(Level, "Select Distinct SelectGroup.Level4 From SelectGroup Join GroupMembers ON (GroupMembers.GroupName = SelectGroup.Level4) where GroupMembers.MobilNr = '" & Session.Item("UMobileNR") & "' AND SelectGroup.Level3 = '" & Session.Item("CSelection") & "'")
                End If
        End Select
        CheckLists(Level)

        Session.Item("LevelSelected") = Level
        TextBox1.Text = Session.Item("CSelection")
        TextBox1.ToolTip = TextBox1.Text
    End Sub
**************************************

Sorry for taking up space here again!

------------------------
All help is Good help!
Regards
Michael