querying a csv text array

hi,

i am trying to run a asp sql query containging many tables.

the main table though is a simple ID based table contains all ids from the
other tables.

eg:

K_Nr Bundesland Bezirk Index.Branch
1 2 2 1,2,3
2 5 3 2,3
3 2 2 2
4 2 2 2,5,8

etc.

BRANCH TABLE:

1 branch1
2 branch2
3 branch3
4 branch4

etc

i can easily acces all records that contain customer_region=2 or customer_nr
= 1 and so on.

but where i am having difficulties is where i need to access all Branches in
Region=2 - "as text"

due to the commas in Index.Branch I had to make the field "text" which then
confilcts against the "number" field in Branch .Table

i can run a loop on the Index.Branch field, but then i get a unordered list
that is not grouped together.


REQUIRED RESULT FOR BEZIRK 2

Index.Branch
branch1
branch2
branch3
branch5
branch8

if it helps here is my SQL

SELECT DISTINCT Index.K_Nr, Index.Bundesland, Index.Bezirk, Index.Branche,
Bundesland.BL_Bundesland
FROM branchen, (Bundesland INNER JOIN (BL_Bezirke INNER JOIN ([Index] INNER
JOIN Kunden ON Index.K_Nr = Kunden.K_nr) ON BL_Bezirke.BezirkID =
Index.Bezirk) ON (BL_Bezirke.BL_Nr = Bundesland.BL_Nr) AND (Bundesland.BL_Nr
= Index.Bundesland)) INNER JOIN Auftrag ON Kunden.K_nr = Auftrag.A_K_Nr
WHERE (((Index.Bezirk)=17) AND ((Index.Branche) Like "*204*"));


Do While Not Rs2.EOF

Response.Write ">" & Rs2("Bezirk") & "
"

MyString = Rs2("Index.Branch")
MyArray = Split(MyString,",")

For i=0 to UBound(MyArray)
Set conn = Server.CreateObject("ADODB.Connection")
connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.MapPath("database/main.mdb")
conn.Open connstr

SQL1 = "Select branchen.* from branchen where Branch= '" &
myArray(i) & "'"
set Rs3=conn.execute(SQL1)

response.write "   " & Rs3("B_nr") & " - " &
Rs3("B_Branche") & "
"
Next
Rs2.MoveNext

Response.Write("


")
Loop


any help is really appreciated.

thanks in advance

nudge

Re: querying a csv text array

Nudge wrote:
> hi,
>
> i am trying to run a asp sql query containging many tables.
>
> the main table though is a simple ID based table contains all ids
> from the other tables.
>
> eg:
>
> K_Nr Bundesland Bezirk Index.Branch
> 1 2 2 1,2,3
> 2 5 3 2,3
> 3 2 2 2
> 4 2 2 2,5,8
>
> etc.

Bad, bad bad bad bad.

1. This is the minor problem: You have a nonstandard character in the
"Index.Branch" filed, requiring you to take special precautions whenever you
query that field.

2. This is the major problem: You are storing multiple pieces of data in the
Index.Branch field, which is a violation of first normal form. It is this
violation that is causing your problem now.

What you have here is a "many-to-many" relationship between these two
tables. I.E. many records in the first table can be associated with/related
to many records in the branch table. The way to resolve this relationship is
through the use of a "bridge" table (other people have other names for this
type of table). In this case, the bridge table would look like this:

K_Nr Bundesland Bezirk Branch
1 2 2 1
1 2 2 2
1 2 2 3
2 5 3 2
2 5 3 3
etc.

This makes it possible to write queries without having to break up multiple
pieces of data in a single field.

Give it a try and see for yourself. And do yourself a favor: use only the
letters a-Z for your database object names.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: querying a csv text array

hi bob, many thanks for your quick response.

the first problem isn't a problem as i wrote this just for the post to
make it clearer for the readers.

the second tip you gave really helped thanks. it's what i was actually
trying to do - thanks for clearing it up for me.

take care


*** Sent via Developersdex http://www.developersdex.com ***

Re: querying a csv text array

Nigel, a table by definition is unordered. If you are looking at the data
and want it "grouped together" then use an ORDER BY clause... that's what
it's for.

--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.




"nigel robertson" wrote in message
news:#xcFgPfNFHA.4028@tk2msftngp13.phx.gbl...
> hi bob, many thanks for your quick response.
>
> the first problem isn't a problem as i wrote this just for the post to
> make it clearer for the readers.
>
> the second tip you gave really helped thanks. it's what i was actually
> trying to do - thanks for clearing it up for me.
>
> take care
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

Re: querying a csv text array

"Bob Barrows [MVP]" wrote in message
news:e8it%23qeNFHA.3076@TK2MSFTNGP14.phx.gbl...
:
: Bad, bad bad bad bad.
:

I have to refrain from reading your posts out loud. I couldn't understand
why the dog came in my room moping when I was reading this one and then I
realized, "bad", is her middle name. No-no "Bad" Dog.

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

Re: querying a csv text array

Roland Hall wrote:
> "Bob Barrows [MVP]" wrote in message
> news:e8it%23qeNFHA.3076@TK2MSFTNGP14.phx.gbl...
>>
>> Bad, bad bad bad bad.
>>
>
> I have to refrain from reading your posts out loud. I couldn't
> understand why the dog came in my room moping when I was reading this
> one and then I realized, "bad", is her middle name. No-no "Bad" Dog.

:-)
That was the image I was trying to evoke.

Bob
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"