best way for avoiding multiple selects

[Peter Proost]

Hi group,

has anyone got any suggestions fot the best way to handle this problem, I've
got 3 tables
for example table A, B, and C
table A looks like name, value
table B looks like name, value
table C looks like variablename, value, value

an example would be
Table A: peter 20
Table B: peter 40
Table C: var1 20 40

so from these tree tables I need to fill an other table (D) so that it looks
like name, variablename(fr om table C) so in our example this would be: peter
var1

Now I know how I can do this but I was just wondering what would be the best
way, table A & B contain about 2300 records each, table C about 200. The way
I've gone about this is to inner join table A and B on there key columns so
I got a filled dataset wich looks like peter 20 40, then I read in all
the variables from table C in 2 arraylists, one containing the values and
one containing the names. then use a binarysearch to find the right element
in the values arraylist(each value pair is unique) and thus the index to use
for the name arraylist, the last step is to save the new record. Is this a
to complex solution? Anyone any suggestions?

greetz Peter

[Zorpiedoman]

RE: best way for avoiding multiple selects

If I understand your situation correctly, I would let your database do the
work for you:

Select A.Column1, C.Column1
From C
Inner Join A on A.Column2 = C.Column2
Inner Join B on B.Column2 = C.Column3
Where A.Column1 = B.Column1

OR

Select A.Column1, C.Column1
From A, B, C
Where A.Column2 = C.Column2
And B.Column2 = C.Column3
And A.Column1 = B.Column1

I argue with people all the time over which would be faster, but with only
2600 records, you'd never know the difference.

-Zorpy

[Cor Ligthert]

Re: best way for avoiding multiple selects

Peter,

I have looked more times too your question however I cannot see the relation
with VBNet, can you explain this more?

Cor

[Peter Proost]

Re: best way for avoiding multiple selects

Hi Cor,

first to reply Zorpiedoman and also you,
I can't let the database do the work for me because I haven't got the table
C, I have to generate it based on the tables A and B
[color=blue]
>table A looks like name, value
>table B looks like name, value
>table C looks like variablename, value, value[/color]

So I use vb.net to get and insert the unique value pairs in in table C and
afterwards I use vb.net to migrate tables A & B into one table (table D in
my previous post), so I hope I cleared my original post now.

greetz Peter

Peter wrote:[color=blue]
>an example would be
>Table A: peter 20
>Table B: peter 40
>Table C: var1 20 40[/color]

but I first have to generate the table C, forgot to mention that
[color=blue]
>so from these tree tables I need to fill an other table (D) so that it[/color]
looks[color=blue]
>like name, variablename(fr om table C) so in our example this would be:[/color]
peter[color=blue]
>var1[/color]




"Cor Ligthert" <notmyfirstname @planet.nl> wrote in message
news:ujHKtuiAFH A.2568@TK2MSFTN GP11.phx.gbl...[color=blue]
> Peter,
>
> I have looked more times too your question however I cannot see the[/color]
relation[color=blue]
> with VBNet, can you explain this more?
>
> Cor
>
>[/color]

[Cor Ligthert]

Re: best way for avoiding multiple selects

Peter,

I cannot see what is a table in your message?
Are that datatables (VBNet) or databasetables (SQL newsgroups)

Cor

[Jay B. Harlow [MVP - Outlook]]

Re: best way for avoiding multiple selects

Peter,
You should be able to use a Select Distinct & Union to generate Table C on
your SQL Server, then you can join this temporary table back to Table A & B
(as Zorpiedoman showed) to get the result.

What is the "variablena me" in C? Are there only 2 'value' columns on C or
multiple value columns?

Hope this helps
Jay


"Peter Proost" <pproost@nospam .hotmail.com> wrote in message
news:efDyO2iAFH A.3492@TK2MSFTN GP12.phx.gbl...[color=blue]
> Hi Cor,
>
> first to reply Zorpiedoman and also you,
> I can't let the database do the work for me because I haven't got the
> table
> C, I have to generate it based on the tables A and B
>[color=green]
>>table A looks like name, value
>>table B looks like name, value
>>table C looks like variablename, value, value[/color]
>
> So I use vb.net to get and insert the unique value pairs in in table C and
> afterwards I use vb.net to migrate tables A & B into one table (table D in
> my previous post), so I hope I cleared my original post now.
>
> greetz Peter
>
> Peter wrote:[color=green]
>>an example would be
>>Table A: peter 20
>>Table B: peter 40
>>Table C: var1 20 40[/color]
>
> but I first have to generate the table C, forgot to mention that
>[color=green]
>>so from these tree tables I need to fill an other table (D) so that it[/color]
> looks[color=green]
>>like name, variablename(fr om table C) so in our example this would be:[/color]
> peter[color=green]
>>var1[/color]
>
>
>
>
> "Cor Ligthert" <notmyfirstname @planet.nl> wrote in message
> news:ujHKtuiAFH A.2568@TK2MSFTN GP11.phx.gbl...[color=green]
>> Peter,
>>
>> I have looked more times too your question however I cannot see the[/color]
> relation[color=green]
>> with VBNet, can you explain this more?
>>
>> Cor
>>
>>[/color]
>
>[/color]

[Peter Proost]

Re: best way for avoiding multiple selects

I'll try to explain it as good as my english lets me, I have got 2 tables in
sql server (assume we call them A & B) the two tables have the same
structure.

Table A & B (the columns named data or just some irrelevant columns):
Code
index
data
data
data
data
number

so an example of a couple of records in table A would be:

Code Index Data Data Data Data Number
1 1 ... ... ... . .. 15
1 2 ... ... ... ... 15
1 3 ... ... ... ... 84

in table B these records look like this

1 1 ... ... ... ... 26
1 2 ... ... ... ... 68
1 3 ... ... ... ... 67

So far this is all sql stuff, now from these 2 tables I need to go to one
table (C) that looks like tables A & B but in the number column change the
number by a code (foreign key to an other table (D)), first I'll explain how
table D looks (table D is empty, I only have got tables A&B which are
filled)

Table D:
Material
Code
Value

No I want to fill table D, and I do this using a .net program, in the
program I select "select a.code, a.index, a.number, b.number from tableA a
inner join tableB b on a.code = b.code and a.index = b.index" in a dataset
and loop through the dataset and if there doesn't exist a record in tableD I
create it

So after my routine table D contains these records
Material Code Value
A var1 15
B var1 26
A var2 15
B var2 68
A var3 84
B var3 67

After table D is filled I can fill table C again using my .net program so
that I've got one table looking like this:
Code Index Data Data Data Data number
1 1 ... ... ... ... var1
1 2 ... ... ... ... var2
1 3 ... ... ... ... var3

I hope you guys can follow what I'm trying to do, I think this can't all be
done with sql statements, so if I do it using .net is it bad if I do a lot
of selects (using dataadapter and datasets to get the right code from
tableD) or is it better if I first create 2 arraylists, one containing the
code from table D (var1,var2,var3 ) and an other one containing the value
pairs (15#26,15#68,84 #67) and the use tableC.number =
arraylistNames( arraylistValues .indexof(15#26) )

Sorry for all the trouble for not explaining it clear enough

greetz Peter

"Jay B. Harlow [MVP - Outlook]" <Jay_Harlow_MVP @msn.com> wrote in message
news:#AVKV2kAFH A.2792@TK2MSFTN GP15.phx.gbl...[color=blue]
> Peter,
> You should be able to use a Select Distinct & Union to generate Table C on
> your SQL Server, then you can join this temporary table back to Table A &[/color]
B[color=blue]
> (as Zorpiedoman showed) to get the result.
>
> What is the "variablena me" in C? Are there only 2 'value' columns on C or
> multiple value columns?
>
> Hope this helps
> Jay
>
>
> "Peter Proost" <pproost@nospam .hotmail.com> wrote in message
> news:efDyO2iAFH A.3492@TK2MSFTN GP12.phx.gbl...[color=green]
> > Hi Cor,
> >
> > first to reply Zorpiedoman and also you,
> > I can't let the database do the work for me because I haven't got the
> > table
> > C, I have to generate it based on the tables A and B
> >[color=darkred]
> >>table A looks like name, value
> >>table B looks like name, value
> >>table C looks like variablename, value, value[/color]
> >
> > So I use vb.net to get and insert the unique value pairs in in table C[/color][/color]
and[color=blue][color=green]
> > afterwards I use vb.net to migrate tables A & B into one table (table D[/color][/color]
in[color=blue][color=green]
> > my previous post), so I hope I cleared my original post now.
> >
> > greetz Peter
> >
> > Peter wrote:[color=darkred]
> >>an example would be
> >>Table A: peter 20
> >>Table B: peter 40
> >>Table C: var1 20 40[/color]
> >
> > but I first have to generate the table C, forgot to mention that
> >[color=darkred]
> >>so from these tree tables I need to fill an other table (D) so that it[/color]
> > looks[color=darkred]
> >>like name, variablename(fr om table C) so in our example this would be:[/color]
> > peter[color=darkred]
> >>var1[/color]
> >
> >
> >
> >
> > "Cor Ligthert" <notmyfirstname @planet.nl> wrote in message
> > news:ujHKtuiAFH A.2568@TK2MSFTN GP11.phx.gbl...[color=darkred]
> >> Peter,
> >>
> >> I have looked more times too your question however I cannot see the[/color]
> > relation[color=darkred]
> >> with VBNet, can you explain this more?
> >>
> >> Cor
> >>
> >>[/color]
> >
> >[/color]
>
>[/color]

[Cor Ligthert]

Re: best way for avoiding multiple selects

Peter,

It was hard to understand for me (has nothing to do with your English, it
would have been the same in Dutch), however the word "distinct" in Jay's
message helped me to find what I think you are up to.

I have once made a standard sample for this, maybe you can make from this
what you want?
It is a complete function sample, the distictkey is in this case of course
your code and your index together and you have to use += on some places
depending on the values which you want to add. When this sample is not
enough, reply than..

Me.DataGrid1.Da taSource = distinct(dt, "MyDistinctElem ent")
End Sub
Public Function distinct(ByVal dt As DataTable, _
ByVal dist As String) As DataTable
Dim dtclone As DataTable = dt.Clone
Dim dv As New DataView(dt)
dv.Sort = dist
Dim myselold As String = ""
For i As Integer = 0 To dv.Count - 1
If myselold <> dv(i)(dist).ToS tring Then
Dim drn As DataRow = dtclone.NewRow
For y As Integer = 0 To drn.ItemArray.L ength - 1
drn(y) = dv(i)(y)
Next
myselold = dv(i)(dist).ToS tring
dtclone.Rows.Ad d(drn)
End If
Next
Return dtclone
End Function

I hope this helps a little bit?

Cor

[Peter Proost]

Re: best way for avoiding multiple selects

Cor,

thanks for your reply, I know it's not always easy to explain a problem just
writing but I would already be happy if know an answer for this:

Peter wrote:[color=blue]
>is it bad if I do a lot
>of selects (using dataadapter and datasets to get the right code from
>tableD) or is it better if I first create 2 arraylists, one containing the
>code from table D (var1,var2,var3 ) and an other one containing the value
>pairs (15#26,15#68,84 #67) and the use tableC.number =
>arraylistNames (arraylistValue s.indexof(15#26 ))[/color]

Thanks for the function I'm sure it will come in handy one day

greetz Peter


"Cor Ligthert" <notmyfirstname @planet.nl> wrote in message
news:eteXp8rAFH A.3744@TK2MSFTN GP15.phx.gbl...[color=blue]
> Peter,
>
> It was hard to understand for me (has nothing to do with your English, it
> would have been the same in Dutch), however the word "distinct" in Jay's
> message helped me to find what I think you are up to.
>
> I have once made a standard sample for this, maybe you can make from this
> what you want?
> It is a complete function sample, the distictkey is in this case of course
> your code and your index together and you have to use += on some places
> depending on the values which you want to add. When this sample is not
> enough, reply than..
>
> Me.DataGrid1.Da taSource = distinct(dt, "MyDistinctElem ent")
> End Sub
> Public Function distinct(ByVal dt As DataTable, _
> ByVal dist As String) As DataTable
> Dim dtclone As DataTable = dt.Clone
> Dim dv As New DataView(dt)
> dv.Sort = dist
> Dim myselold As String = ""
> For i As Integer = 0 To dv.Count - 1
> If myselold <> dv(i)(dist).ToS tring Then
> Dim drn As DataRow = dtclone.NewRow
> For y As Integer = 0 To drn.ItemArray.L ength - 1
> drn(y) = dv(i)(y)
> Next
> myselold = dv(i)(dist).ToS tring
> dtclone.Rows.Ad d(drn)
> End If
> Next
> Return dtclone
> End Function
>
> I hope this helps a little bit?
>
> Cor
>
>[/color]

[Jay B. Harlow [MVP - Outlook]]

Re: best way for avoiding multiple selects

Peter,
What kind of database? Access, SQL Server 7, SQL Server 2000, SQL Server
2005, AS400, other?

From what you have stated I believe it can be done in SQL, Joe Celkos' book
"SQL Form Smarties: Advanced SQL Programming - Second Edition" from Morgan
Kaufmann may help with some of the details. The question is more (based on
which database) can it be done with a single SQL statement or a series of
SQL statements. Also from a readability POV would one even want to attempt a
single statement.

The flavor of database is important as each supports different features, for
example SQL Server 2005 & the AS400 have significantly more features (SQL
language constructs) to use then Access.

I will try to look at this later today, for an complete SQL solution.

When you are creating table D & table C, where does "var1" come from? Your
SQL statement looks like it should simply be a 1 not var1!

Which comes first table C or table D?

NOTE: From what you've stated so far I would do all of the work in a single
DataSet with multiple DataTables that have primary keys set.

Hope this helps
Jay

"Peter Proost" <pproost@nospam .hotmail.com> wrote in message
news:eb1KBdrAFH A.2932@TK2MSFTN GP10.phx.gbl...[color=blue]
> I'll try to explain it as good as my english lets me, I have got 2 tables
> in
> sql server (assume we call them A & B) the two tables have the same
> structure.
>
> Table A & B (the columns named data or just some irrelevant columns):
> Code
> index
> data
> data
> data
> data
> number
>
> so an example of a couple of records in table A would be:
>
> Code Index Data Data Data Data Number
> 1 1 ... ... ... . .. 15
> 1 2 ... ... ... ... 15
> 1 3 ... ... ... ... 84
>
> in table B these records look like this
>
> 1 1 ... ... ... ... 26
> 1 2 ... ... ... ... 68
> 1 3 ... ... ... ... 67
>
> So far this is all sql stuff, now from these 2 tables I need to go to one
> table (C) that looks like tables A & B but in the number column change the
> number by a code (foreign key to an other table (D)), first I'll explain
> how
> table D looks (table D is empty, I only have got tables A&B which are
> filled)
>
> Table D:
> Material
> Code
> Value
>
> No I want to fill table D, and I do this using a .net program, in the
> program I select "select a.code, a.index, a.number, b.number from tableA a
> inner join tableB b on a.code = b.code and a.index = b.index" in a dataset
> and loop through the dataset and if there doesn't exist a record in tableD
> I
> create it
>
> So after my routine table D contains these records
> Material Code Value
> A var1 15
> B var1 26
> A var2 15
> B var2 68
> A var3 84
> B var3 67
>
> After table D is filled I can fill table C again using my .net program so
> that I've got one table looking like this:
> Code Index Data Data Data Data number
> 1 1 ... ... ... ... var1
> 1 2 ... ... ... ... var2
> 1 3 ... ... ... ... var3
>
> I hope you guys can follow what I'm trying to do, I think this can't all
> be
> done with sql statements, so if I do it using .net is it bad if I do a
> lot
> of selects (using dataadapter and datasets to get the right code from
> tableD) or is it better if I first create 2 arraylists, one containing the
> code from table D (var1,var2,var3 ) and an other one containing the value
> pairs (15#26,15#68,84 #67) and the use tableC.number =
> arraylistNames( arraylistValues .indexof(15#26) )
>
> Sorry for all the trouble for not explaining it clear enough
>
> greetz Peter
>
> "Jay B. Harlow [MVP - Outlook]" <Jay_Harlow_MVP @msn.com> wrote in message
> news:#AVKV2kAFH A.2792@TK2MSFTN GP15.phx.gbl...[color=green]
>> Peter,
>> You should be able to use a Select Distinct & Union to generate Table C
>> on
>> your SQL Server, then you can join this temporary table back to Table A &[/color]
> B[color=green]
>> (as Zorpiedoman showed) to get the result.
>>
>> What is the "variablena me" in C? Are there only 2 'value' columns on C or
>> multiple value columns?
>>
>> Hope this helps
>> Jay
>>
>>
>> "Peter Proost" <pproost@nospam .hotmail.com> wrote in message
>> news:efDyO2iAFH A.3492@TK2MSFTN GP12.phx.gbl...[color=darkred]
>> > Hi Cor,
>> >
>> > first to reply Zorpiedoman and also you,
>> > I can't let the database do the work for me because I haven't got the
>> > table
>> > C, I have to generate it based on the tables A and B
>> >
>> >>table A looks like name, value
>> >>table B looks like name, value
>> >>table C looks like variablename, value, value
>> >
>> > So I use vb.net to get and insert the unique value pairs in in table C[/color][/color]
> and[color=green][color=darkred]
>> > afterwards I use vb.net to migrate tables A & B into one table (table D[/color][/color]
> in[color=green][color=darkred]
>> > my previous post), so I hope I cleared my original post now.
>> >
>> > greetz Peter
>> >
>> > Peter wrote:
>> >>an example would be
>> >>Table A: peter 20
>> >>Table B: peter 40
>> >>Table C: var1 20 40
>> >
>> > but I first have to generate the table C, forgot to mention that
>> >
>> >>so from these tree tables I need to fill an other table (D) so that it
>> > looks
>> >>like name, variablename(fr om table C) so in our example this would be:
>> > peter
>> >>var1
>> >
>> >
>> >
>> >
>> > "Cor Ligthert" <notmyfirstname @planet.nl> wrote in message
>> > news:ujHKtuiAFH A.2568@TK2MSFTN GP11.phx.gbl...
>> >> Peter,
>> >>
>> >> I have looked more times too your question however I cannot see the
>> > relation
>> >> with VBNet, can you explain this more?
>> >>
>> >> Cor
>> >>
>> >>
>> >
>> >[/color]
>>
>>[/color]
>
>[/color]

[Peter Proost]

Re: best way for avoiding multiple selects

Hi Jay,

Thanks for your reply, I have to create the var1 part because the user later
on has to select them an he wants them called var1, var2, var... , the table
D gets created before table C, the database is SQL Server 2000. Thnx for
your time & help

greetz Peter

"Jay B. Harlow [MVP - Outlook]" <Jay_Harlow_MVP @msn.com> wrote in message
news:OZq#3AvAFH A.1392@tk2msftn gp13.phx.gbl...[color=blue]
> Peter,
> What kind of database? Access, SQL Server 7, SQL Server 2000, SQL Server
> 2005, AS400, other?
>
> From what you have stated I believe it can be done in SQL, Joe Celkos'[/color]
book[color=blue]
> "SQL Form Smarties: Advanced SQL Programming - Second Edition" from Morgan
> Kaufmann may help with some of the details. The question is more (based on
> which database) can it be done with a single SQL statement or a series of
> SQL statements. Also from a readability POV would one even want to attempt[/color]
a[color=blue]
> single statement.
>
> The flavor of database is important as each supports different features,[/color]
for[color=blue]
> example SQL Server 2005 & the AS400 have significantly more features (SQL
> language constructs) to use then Access.
>
> I will try to look at this later today, for an complete SQL solution.
>
> When you are creating table D & table C, where does "var1" come from? Your
> SQL statement looks like it should simply be a 1 not var1!
>
> Which comes first table C or table D?
>
> NOTE: From what you've stated so far I would do all of the work in a[/color]
single[color=blue]
> DataSet with multiple DataTables that have primary keys set.
>
> Hope this helps
> Jay
>
> "Peter Proost" <pproost@nospam .hotmail.com> wrote in message
> news:eb1KBdrAFH A.2932@TK2MSFTN GP10.phx.gbl...[color=green]
> > I'll try to explain it as good as my english lets me, I have got 2[/color][/color]
tables[color=blue][color=green]
> > in
> > sql server (assume we call them A & B) the two tables have the same
> > structure.
> >
> > Table A & B (the columns named data or just some irrelevant columns):
> > Code
> > index
> > data
> > data
> > data
> > data
> > number
> >
> > so an example of a couple of records in table A would be:
> >
> > Code Index Data Data Data Data Number
> > 1 1 ... ... ... . .. 15
> > 1 2 ... ... ... ... 15
> > 1 3 ... ... ... ... 84
> >
> > in table B these records look like this
> >
> > 1 1 ... ... ... ... 26
> > 1 2 ... ... ... ... 68
> > 1 3 ... ... ... ... 67
> >
> > So far this is all sql stuff, now from these 2 tables I need to go to[/color][/color]
one[color=blue][color=green]
> > table (C) that looks like tables A & B but in the number column change[/color][/color]
the[color=blue][color=green]
> > number by a code (foreign key to an other table (D)), first I'll explain
> > how
> > table D looks (table D is empty, I only have got tables A&B which are
> > filled)
> >
> > Table D:
> > Material
> > Code
> > Value
> >
> > No I want to fill table D, and I do this using a .net program, in the
> > program I select "select a.code, a.index, a.number, b.number from tableA[/color][/color]
a[color=blue][color=green]
> > inner join tableB b on a.code = b.code and a.index = b.index" in a[/color][/color]
dataset[color=blue][color=green]
> > and loop through the dataset and if there doesn't exist a record in[/color][/color]
tableD[color=blue][color=green]
> > I
> > create it
> >
> > So after my routine table D contains these records
> > Material Code Value
> > A var1 15
> > B var1 26
> > A var2 15
> > B var2 68
> > A var3 84
> > B var3 67
> >
> > After table D is filled I can fill table C again using my .net program[/color][/color]
so[color=blue][color=green]
> > that I've got one table looking like this:
> > Code Index Data Data Data Data number
> > 1 1 ... ... ... ... var1
> > 1 2 ... ... ... ... var2
> > 1 3 ... ... ... ... var3
> >
> > I hope you guys can follow what I'm trying to do, I think this can't all
> > be
> > done with sql statements, so if I do it using .net is it bad if I do a
> > lot
> > of selects (using dataadapter and datasets to get the right code from
> > tableD) or is it better if I first create 2 arraylists, one containing[/color][/color]
the[color=blue][color=green]
> > code from table D (var1,var2,var3 ) and an other one containing the value
> > pairs (15#26,15#68,84 #67) and the use tableC.number =
> > arraylistNames( arraylistValues .indexof(15#26) )
> >
> > Sorry for all the trouble for not explaining it clear enough
> >
> > greetz Peter
> >
> > "Jay B. Harlow [MVP - Outlook]" <Jay_Harlow_MVP @msn.com> wrote in[/color][/color]
message[color=blue][color=green]
> > news:#AVKV2kAFH A.2792@TK2MSFTN GP15.phx.gbl...[color=darkred]
> >> Peter,
> >> You should be able to use a Select Distinct & Union to generate Table C
> >> on
> >> your SQL Server, then you can join this temporary table back to Table A[/color][/color][/color]
&[color=blue][color=green]
> > B[color=darkred]
> >> (as Zorpiedoman showed) to get the result.
> >>
> >> What is the "variablena me" in C? Are there only 2 'value' columns on C[/color][/color][/color]
or[color=blue][color=green][color=darkred]
> >> multiple value columns?
> >>
> >> Hope this helps
> >> Jay
> >>
> >>
> >> "Peter Proost" <pproost@nospam .hotmail.com> wrote in message
> >> news:efDyO2iAFH A.3492@TK2MSFTN GP12.phx.gbl...
> >> > Hi Cor,
> >> >
> >> > first to reply Zorpiedoman and also you,
> >> > I can't let the database do the work for me because I haven't got the
> >> > table
> >> > C, I have to generate it based on the tables A and B
> >> >
> >> >>table A looks like name, value
> >> >>table B looks like name, value
> >> >>table C looks like variablename, value, value
> >> >
> >> > So I use vb.net to get and insert the unique value pairs in in table[/color][/color][/color]
C[color=blue][color=green]
> > and[color=darkred]
> >> > afterwards I use vb.net to migrate tables A & B into one table (table[/color][/color][/color]
D[color=blue][color=green]
> > in[color=darkred]
> >> > my previous post), so I hope I cleared my original post now.
> >> >
> >> > greetz Peter
> >> >
> >> > Peter wrote:
> >> >>an example would be
> >> >>Table A: peter 20
> >> >>Table B: peter 40
> >> >>Table C: var1 20 40
> >> >
> >> > but I first have to generate the table C, forgot to mention that
> >> >
> >> >>so from these tree tables I need to fill an other table (D) so that[/color][/color][/color]
it[color=blue][color=green][color=darkred]
> >> > looks
> >> >>like name, variablename(fr om table C) so in our example this would[/color][/color][/color]
be:[color=blue][color=green][color=darkred]
> >> > peter
> >> >>var1
> >> >
> >> >
> >> >
> >> >
> >> > "Cor Ligthert" <notmyfirstname @planet.nl> wrote in message
> >> > news:ujHKtuiAFH A.2568@TK2MSFTN GP11.phx.gbl...
> >> >> Peter,
> >> >>
> >> >> I have looked more times too your question however I cannot see the
> >> > relation
> >> >> with VBNet, can you explain this more?
> >> >>
> >> >> Cor
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>[/color]
> >
> >[/color]
>
>[/color]

[Jay B. Harlow [MVP - Outlook]]

Re: best way for avoiding multiple selects

Peter,
But created it (var1) from where?

It appears to be ("var" & index), however you might be using a second table
to look it up...

Thanks
Jay


"Peter Proost" <pproost@nospam .hotmail.com> wrote in message
news:uSekbPvAFH A.3988@TK2MSFTN GP11.phx.gbl...[color=blue]
> Hi Jay,
>
> Thanks for your reply, I have to create the var1 part because the user
> later
> on has to select them an he wants them called var1, var2, var... , the
> table
> D gets created before table C, the database is SQL Server 2000. Thnx for
> your time & help
>
> greetz Peter
>
> "Jay B. Harlow [MVP - Outlook]" <Jay_Harlow_MVP @msn.com> wrote in message
> news:OZq#3AvAFH A.1392@tk2msftn gp13.phx.gbl...[color=green]
>> Peter,
>> What kind of database? Access, SQL Server 7, SQL Server 2000, SQL Server
>> 2005, AS400, other?
>>
>> From what you have stated I believe it can be done in SQL, Joe Celkos'[/color]
> book[color=green]
>> "SQL Form Smarties: Advanced SQL Programming - Second Edition" from
>> Morgan
>> Kaufmann may help with some of the details. The question is more (based
>> on
>> which database) can it be done with a single SQL statement or a series of
>> SQL statements. Also from a readability POV would one even want to
>> attempt[/color]
> a[color=green]
>> single statement.
>>
>> The flavor of database is important as each supports different features,[/color]
> for[color=green]
>> example SQL Server 2005 & the AS400 have significantly more features (SQL
>> language constructs) to use then Access.
>>
>> I will try to look at this later today, for an complete SQL solution.
>>
>> When you are creating table D & table C, where does "var1" come from?
>> Your
>> SQL statement looks like it should simply be a 1 not var1!
>>
>> Which comes first table C or table D?
>>
>> NOTE: From what you've stated so far I would do all of the work in a[/color]
> single[color=green]
>> DataSet with multiple DataTables that have primary keys set.
>>
>> Hope this helps
>> Jay
>>
>> "Peter Proost" <pproost@nospam .hotmail.com> wrote in message
>> news:eb1KBdrAFH A.2932@TK2MSFTN GP10.phx.gbl...[color=darkred]
>> > I'll try to explain it as good as my english lets me, I have got 2[/color][/color]
> tables[color=green][color=darkred]
>> > in
>> > sql server (assume we call them A & B) the two tables have the same
>> > structure.
>> >
>> > Table A & B (the columns named data or just some irrelevant columns):
>> > Code
>> > index
>> > data
>> > data
>> > data
>> > data
>> > number
>> >
>> > so an example of a couple of records in table A would be:
>> >
>> > Code Index Data Data Data Data Number
>> > 1 1 ... ... ... . .. 15
>> > 1 2 ... ... ... ... 15
>> > 1 3 ... ... ... ... 84
>> >
>> > in table B these records look like this
>> >
>> > 1 1 ... ... ... ... 26
>> > 1 2 ... ... ... ... 68
>> > 1 3 ... ... ... ... 67
>> >
>> > So far this is all sql stuff, now from these 2 tables I need to go to[/color][/color]
> one[color=green][color=darkred]
>> > table (C) that looks like tables A & B but in the number column change[/color][/color]
> the[color=green][color=darkred]
>> > number by a code (foreign key to an other table (D)), first I'll
>> > explain
>> > how
>> > table D looks (table D is empty, I only have got tables A&B which are
>> > filled)
>> >
>> > Table D:
>> > Material
>> > Code
>> > Value
>> >
>> > No I want to fill table D, and I do this using a .net program, in the
>> > program I select "select a.code, a.index, a.number, b.number from
>> > tableA[/color][/color]
> a[color=green][color=darkred]
>> > inner join tableB b on a.code = b.code and a.index = b.index" in a[/color][/color]
> dataset[color=green][color=darkred]
>> > and loop through the dataset and if there doesn't exist a record in[/color][/color]
> tableD[color=green][color=darkred]
>> > I
>> > create it
>> >
>> > So after my routine table D contains these records
>> > Material Code Value
>> > A var1 15
>> > B var1 26
>> > A var2 15
>> > B var2 68
>> > A var3 84
>> > B var3 67
>> >
>> > After table D is filled I can fill table C again using my .net program[/color][/color]
> so[color=green][color=darkred]
>> > that I've got one table looking like this:
>> > Code Index Data Data Data Data number
>> > 1 1 ... ... ... ... var1
>> > 1 2 ... ... ... ... var2
>> > 1 3 ... ... ... ... var3
>> >
>> > I hope you guys can follow what I'm trying to do, I think this can't
>> > all
>> > be
>> > done with sql statements, so if I do it using .net is it bad if I do a
>> > lot
>> > of selects (using dataadapter and datasets to get the right code from
>> > tableD) or is it better if I first create 2 arraylists, one containing[/color][/color]
> the[color=green][color=darkred]
>> > code from table D (var1,var2,var3 ) and an other one containing the
>> > value
>> > pairs (15#26,15#68,84 #67) and the use tableC.number =
>> > arraylistNames( arraylistValues .indexof(15#26) )
>> >
>> > Sorry for all the trouble for not explaining it clear enough
>> >
>> > greetz Peter
>> >
>> > "Jay B. Harlow [MVP - Outlook]" <Jay_Harlow_MVP @msn.com> wrote in[/color][/color]
> message[color=green][color=darkred]
>> > news:#AVKV2kAFH A.2792@TK2MSFTN GP15.phx.gbl...
>> >> Peter,
>> >> You should be able to use a Select Distinct & Union to generate Table
>> >> C
>> >> on
>> >> your SQL Server, then you can join this temporary table back to Table
>> >> A[/color][/color]
> &[color=green][color=darkred]
>> > B
>> >> (as Zorpiedoman showed) to get the result.
>> >>
>> >> What is the "variablena me" in C? Are there only 2 'value' columns on C[/color][/color]
> or[color=green][color=darkred]
>> >> multiple value columns?
>> >>
>> >> Hope this helps
>> >> Jay
>> >>
>> >>
>> >> "Peter Proost" <pproost@nospam .hotmail.com> wrote in message
>> >> news:efDyO2iAFH A.3492@TK2MSFTN GP12.phx.gbl...
>> >> > Hi Cor,
>> >> >
>> >> > first to reply Zorpiedoman and also you,
>> >> > I can't let the database do the work for me because I haven't got
>> >> > the
>> >> > table
>> >> > C, I have to generate it based on the tables A and B
>> >> >
>> >> >>table A looks like name, value
>> >> >>table B looks like name, value
>> >> >>table C looks like variablename, value, value
>> >> >
>> >> > So I use vb.net to get and insert the unique value pairs in in table[/color][/color]
> C[color=green][color=darkred]
>> > and
>> >> > afterwards I use vb.net to migrate tables A & B into one table
>> >> > (table[/color][/color]
> D[color=green][color=darkred]
>> > in
>> >> > my previous post), so I hope I cleared my original post now.
>> >> >
>> >> > greetz Peter
>> >> >
>> >> > Peter wrote:
>> >> >>an example would be
>> >> >>Table A: peter 20
>> >> >>Table B: peter 40
>> >> >>Table C: var1 20 40
>> >> >
>> >> > but I first have to generate the table C, forgot to mention that
>> >> >
>> >> >>so from these tree tables I need to fill an other table (D) so that[/color][/color]
> it[color=green][color=darkred]
>> >> > looks
>> >> >>like name, variablename(fr om table C) so in our example this would[/color][/color]
> be:[color=green][color=darkred]
>> >> > peter
>> >> >>var1
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > "Cor Ligthert" <notmyfirstname @planet.nl> wrote in message
>> >> > news:ujHKtuiAFH A.2568@TK2MSFTN GP11.phx.gbl...
>> >> >> Peter,
>> >> >>
>> >> >> I have looked more times too your question however I cannot see the
>> >> > relation
>> >> >> with VBNet, can you explain this more?
>> >> >>
>> >> >> Cor
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >[/color]
>>
>>[/color]
>
>[/color]

[Peter Proost]

Re: best way for avoiding multiple selects

I just create the identical value pairs in table D and the 1 is a counter in
my program so when I create a value pair the counter gets +=1 that's how I
get var1, var2,var3,var.. .

greetz Peter

"Jay B. Harlow [MVP - Outlook]" <Jay_Harlow_MVP @msn.com> wrote in message
news:uhvsUWvAFH A.2932@TK2MSFTN GP10.phx.gbl...[color=blue]
> Peter,
> But created it (var1) from where?
>
> It appears to be ("var" & index), however you might be using a second[/color]
table[color=blue]
> to look it up...
>
> Thanks
> Jay
>
>
> "Peter Proost" <pproost@nospam .hotmail.com> wrote in message
> news:uSekbPvAFH A.3988@TK2MSFTN GP11.phx.gbl...[color=green]
> > Hi Jay,
> >
> > Thanks for your reply, I have to create the var1 part because the user
> > later
> > on has to select them an he wants them called var1, var2, var... , the
> > table
> > D gets created before table C, the database is SQL Server 2000. Thnx for
> > your time & help
> >
> > greetz Peter
> >
> > "Jay B. Harlow [MVP - Outlook]" <Jay_Harlow_MVP @msn.com> wrote in[/color][/color]
message[color=blue][color=green]
> > news:OZq#3AvAFH A.1392@tk2msftn gp13.phx.gbl...[color=darkred]
> >> Peter,
> >> What kind of database? Access, SQL Server 7, SQL Server 2000, SQL[/color][/color][/color]
Server[color=blue][color=green][color=darkred]
> >> 2005, AS400, other?
> >>
> >> From what you have stated I believe it can be done in SQL, Joe Celkos'[/color]
> > book[color=darkred]
> >> "SQL Form Smarties: Advanced SQL Programming - Second Edition" from
> >> Morgan
> >> Kaufmann may help with some of the details. The question is more (based
> >> on
> >> which database) can it be done with a single SQL statement or a series[/color][/color][/color]
of[color=blue][color=green][color=darkred]
> >> SQL statements. Also from a readability POV would one even want to
> >> attempt[/color]
> > a[color=darkred]
> >> single statement.
> >>
> >> The flavor of database is important as each supports different[/color][/color][/color]
features,[color=blue][color=green]
> > for[color=darkred]
> >> example SQL Server 2005 & the AS400 have significantly more features[/color][/color][/color]
(SQL[color=blue][color=green][color=darkred]
> >> language constructs) to use then Access.
> >>
> >> I will try to look at this later today, for an complete SQL solution.
> >>
> >> When you are creating table D & table C, where does "var1" come from?
> >> Your
> >> SQL statement looks like it should simply be a 1 not var1!
> >>
> >> Which comes first table C or table D?
> >>
> >> NOTE: From what you've stated so far I would do all of the work in a[/color]
> > single[color=darkred]
> >> DataSet with multiple DataTables that have primary keys set.
> >>
> >> Hope this helps
> >> Jay
> >>
> >> "Peter Proost" <pproost@nospam .hotmail.com> wrote in message
> >> news:eb1KBdrAFH A.2932@TK2MSFTN GP10.phx.gbl...
> >> > I'll try to explain it as good as my english lets me, I have got 2[/color]
> > tables[color=darkred]
> >> > in
> >> > sql server (assume we call them A & B) the two tables have the same
> >> > structure.
> >> >
> >> > Table A & B (the columns named data or just some irrelevant columns):
> >> > Code
> >> > index
> >> > data
> >> > data
> >> > data
> >> > data
> >> > number
> >> >
> >> > so an example of a couple of records in table A would be:
> >> >
> >> > Code Index Data Data Data Data Number
> >> > 1 1 ... ... ... . .. 15
> >> > 1 2 ... ... ... ... 15
> >> > 1 3 ... ... ... ... 84
> >> >
> >> > in table B these records look like this
> >> >
> >> > 1 1 ... ... ... ... 26
> >> > 1 2 ... ... ... ... 68
> >> > 1 3 ... ... ... ... 67
> >> >
> >> > So far this is all sql stuff, now from these 2 tables I need to go to[/color]
> > one[color=darkred]
> >> > table (C) that looks like tables A & B but in the number column[/color][/color][/color]
change[color=blue][color=green]
> > the[color=darkred]
> >> > number by a code (foreign key to an other table (D)), first I'll
> >> > explain
> >> > how
> >> > table D looks (table D is empty, I only have got tables A&B which are
> >> > filled)
> >> >
> >> > Table D:
> >> > Material
> >> > Code
> >> > Value
> >> >
> >> > No I want to fill table D, and I do this using a .net program, in the
> >> > program I select "select a.code, a.index, a.number, b.number from
> >> > tableA[/color]
> > a[color=darkred]
> >> > inner join tableB b on a.code = b.code and a.index = b.index" in a[/color]
> > dataset[color=darkred]
> >> > and loop through the dataset and if there doesn't exist a record in[/color]
> > tableD[color=darkred]
> >> > I
> >> > create it
> >> >
> >> > So after my routine table D contains these records
> >> > Material Code Value
> >> > A var1 15
> >> > B var1 26
> >> > A var2 15
> >> > B var2 68
> >> > A var3 84
> >> > B var3 67
> >> >
> >> > After table D is filled I can fill table C again using my .net[/color][/color][/color]
program[color=blue][color=green]
> > so[color=darkred]
> >> > that I've got one table looking like this:
> >> > Code Index Data Data Data Data number
> >> > 1 1 ... ... ... ... var1
> >> > 1 2 ... ... ... ... var2
> >> > 1 3 ... ... ... ... var3
> >> >
> >> > I hope you guys can follow what I'm trying to do, I think this can't
> >> > all
> >> > be
> >> > done with sql statements, so if I do it using .net is it bad if I do[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> >> > lot
> >> > of selects (using dataadapter and datasets to get the right code from
> >> > tableD) or is it better if I first create 2 arraylists, one[/color][/color][/color]
containing[color=blue][color=green]
> > the[color=darkred]
> >> > code from table D (var1,var2,var3 ) and an other one containing the
> >> > value
> >> > pairs (15#26,15#68,84 #67) and the use tableC.number =
> >> > arraylistNames( arraylistValues .indexof(15#26) )
> >> >
> >> > Sorry for all the trouble for not explaining it clear enough
> >> >
> >> > greetz Peter
> >> >
> >> > "Jay B. Harlow [MVP - Outlook]" <Jay_Harlow_MVP @msn.com> wrote in[/color]
> > message[color=darkred]
> >> > news:#AVKV2kAFH A.2792@TK2MSFTN GP15.phx.gbl...
> >> >> Peter,
> >> >> You should be able to use a Select Distinct & Union to generate[/color][/color][/color]
Table[color=blue][color=green][color=darkred]
> >> >> C
> >> >> on
> >> >> your SQL Server, then you can join this temporary table back to[/color][/color][/color]
Table[color=blue][color=green][color=darkred]
> >> >> A[/color]
> > &[color=darkred]
> >> > B
> >> >> (as Zorpiedoman showed) to get the result.
> >> >>
> >> >> What is the "variablena me" in C? Are there only 2 'value' columns on[/color][/color][/color]
C[color=blue][color=green]
> > or[color=darkred]
> >> >> multiple value columns?
> >> >>
> >> >> Hope this helps
> >> >> Jay
> >> >>
> >> >>
> >> >> "Peter Proost" <pproost@nospam .hotmail.com> wrote in message
> >> >> news:efDyO2iAFH A.3492@TK2MSFTN GP12.phx.gbl...
> >> >> > Hi Cor,
> >> >> >
> >> >> > first to reply Zorpiedoman and also you,
> >> >> > I can't let the database do the work for me because I haven't got
> >> >> > the
> >> >> > table
> >> >> > C, I have to generate it based on the tables A and B
> >> >> >
> >> >> >>table A looks like name, value
> >> >> >>table B looks like name, value
> >> >> >>table C looks like variablename, value, value
> >> >> >
> >> >> > So I use vb.net to get and insert the unique value pairs in in[/color][/color][/color]
table[color=blue][color=green]
> > C[color=darkred]
> >> > and
> >> >> > afterwards I use vb.net to migrate tables A & B into one table
> >> >> > (table[/color]
> > D[color=darkred]
> >> > in
> >> >> > my previous post), so I hope I cleared my original post now.
> >> >> >
> >> >> > greetz Peter
> >> >> >
> >> >> > Peter wrote:
> >> >> >>an example would be
> >> >> >>Table A: peter 20
> >> >> >>Table B: peter 40
> >> >> >>Table C: var1 20 40
> >> >> >
> >> >> > but I first have to generate the table C, forgot to mention that
> >> >> >
> >> >> >>so from these tree tables I need to fill an other table (D) so[/color][/color][/color]
that[color=blue][color=green]
> > it[color=darkred]
> >> >> > looks
> >> >> >>like name, variablename(fr om table C) so in our example this would[/color]
> > be:[color=darkred]
> >> >> > peter
> >> >> >>var1
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Cor Ligthert" <notmyfirstname @planet.nl> wrote in message
> >> >> > news:ujHKtuiAFH A.2568@TK2MSFTN GP11.phx.gbl...
> >> >> >> Peter,
> >> >> >>
> >> >> >> I have looked more times too your question however I cannot see[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> >> >> > relation
> >> >> >> with VBNet, can you explain this more?
> >> >> >>
> >> >> >> Cor
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>[/color]
> >
> >[/color]
>
>[/color]