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(from 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 12 1729
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
Peter,
I have looked more times too your question however I cannot see the relation
with VBNet, can you explain this more?
Cor
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 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: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
lookslike name, variablename(from table C) so in our example this would be:
petervar1
"Cor Ligthert" <no************@planet.nl> wrote in message
news:uj**************@TK2MSFTNGP11.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
Peter,
I cannot see what is a table in your message?
Are that datatables (VBNet) or databasetables (SQL newsgroups)
Cor
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 "variablename" in C? Are there only 2 'value' columns on C or
multiple value columns?
Hope this helps
Jay
"Peter Proost" <pp*****@nospam.hotmail.com> wrote in message
news:ef**************@TK2MSFTNGP12.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 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: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 lookslike name, variablename(from table C) so in our example this would be: petervar1
"Cor Ligthert" <no************@planet.nl> wrote in message news:uj**************@TK2MSFTNGP11.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
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]" <Ja************@msn.com> wrote in message
news:#A**************@TK2MSFTNGP15.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 &
B (as Zorpiedoman showed) to get the result.
What is the "variablename" in C? Are there only 2 'value' columns on C or multiple value columns?
Hope this helps Jay
"Peter Proost" <pp*****@nospam.hotmail.com> wrote in message news:ef**************@TK2MSFTNGP12.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 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: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 lookslike name, variablename(from table C) so in our example this would be: petervar1
"Cor Ligthert" <no************@planet.nl> wrote in message news:uj**************@TK2MSFTNGP11.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
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.DataSource = distinct(dt, "MyDistinctElement")
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).ToString Then
Dim drn As DataRow = dtclone.NewRow
For y As Integer = 0 To drn.ItemArray.Length - 1
drn(y) = dv(i)(y)
Next
myselold = dv(i)(dist).ToString
dtclone.Rows.Add(drn)
End If
Next
Return dtclone
End Function
I hope this helps a little bit?
Cor
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: 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))
Thanks for the function I'm sure it will come in handy one day
greetz Peter
"Cor Ligthert" <no************@planet.nl> wrote in message
news:et**************@TK2MSFTNGP15.phx.gbl... 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.DataSource = distinct(dt, "MyDistinctElement") 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).ToString Then Dim drn As DataRow = dtclone.NewRow For y As Integer = 0 To drn.ItemArray.Length - 1 drn(y) = dv(i)(y) Next myselold = dv(i)(dist).ToString dtclone.Rows.Add(drn) End If Next Return dtclone End Function
I hope this helps a little bit?
Cor
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" <pp*****@nospam.hotmail.com> wrote in message
news:eb**************@TK2MSFTNGP10.phx.gbl... 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]" <Ja************@msn.com> wrote in message news:#A**************@TK2MSFTNGP15.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 & B (as Zorpiedoman showed) to get the result.
What is the "variablename" in C? Are there only 2 'value' columns on C or multiple value columns?
Hope this helps Jay
"Peter Proost" <pp*****@nospam.hotmail.com> wrote in message news:ef**************@TK2MSFTNGP12.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 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: >>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(from table C) so in our example this would be: > peter >>var1 > > > > > "Cor Ligthert" <no************@planet.nl> wrote in message > news:uj**************@TK2MSFTNGP11.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 >> >> > >
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]" <Ja************@msn.com> wrote in message
news:OZ**************@tk2msftngp13.phx.gbl... 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" <pp*****@nospam.hotmail.com> wrote in message news:eb**************@TK2MSFTNGP10.phx.gbl... 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]" <Ja************@msn.com> wrote in
message news:#A**************@TK2MSFTNGP15.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
& B (as Zorpiedoman showed) to get the result.
What is the "variablename" in C? Are there only 2 'value' columns on C
or multiple value columns?
Hope this helps Jay
"Peter Proost" <pp*****@nospam.hotmail.com> wrote in message news:ef**************@TK2MSFTNGP12.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
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: >>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(from table C) so in our example this would
be: > peter >>var1 > > > > > "Cor Ligthert" <no************@planet.nl> wrote in message > news:uj**************@TK2MSFTNGP11.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 >> >> > >
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" <pp*****@nospam.hotmail.com> wrote in message
news:uS**************@TK2MSFTNGP11.phx.gbl... 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]" <Ja************@msn.com> wrote in message news:OZ**************@tk2msftngp13.phx.gbl... 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" <pp*****@nospam.hotmail.com> wrote in message news:eb**************@TK2MSFTNGP10.phx.gbl... > 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]" <Ja************@msn.com> wrote in message > news:#A**************@TK2MSFTNGP15.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 & > B >> (as Zorpiedoman showed) to get the result. >> >> What is the "variablename" in C? Are there only 2 'value' columns on C or >> multiple value columns? >> >> Hope this helps >> Jay >> >> >> "Peter Proost" <pp*****@nospam.hotmail.com> wrote in message >> news:ef**************@TK2MSFTNGP12.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 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: >> >>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(from table C) so in our example this would be: >> > peter >> >>var1 >> > >> > >> > >> > >> > "Cor Ligthert" <no************@planet.nl> wrote in message >> > news:uj**************@TK2MSFTNGP11.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 >> >> >> >> >> > >> > >> >> > >
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]" <Ja************@msn.com> wrote in message
news:uh**************@TK2MSFTNGP10.phx.gbl... 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" <pp*****@nospam.hotmail.com> wrote in message news:uS**************@TK2MSFTNGP11.phx.gbl... 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]" <Ja************@msn.com> wrote in
message news:OZ**************@tk2msftngp13.phx.gbl... 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" <pp*****@nospam.hotmail.com> wrote in message news:eb**************@TK2MSFTNGP10.phx.gbl... > 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]" <Ja************@msn.com> wrote in message > news:#A**************@TK2MSFTNGP15.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 & > B >> (as Zorpiedoman showed) to get the result. >> >> What is the "variablename" in C? Are there only 2 'value' columns on
C or >> multiple value columns? >> >> Hope this helps >> Jay >> >> >> "Peter Proost" <pp*****@nospam.hotmail.com> wrote in message >> news:ef**************@TK2MSFTNGP12.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 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: >> >>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(from table C) so in our example this would be: >> > peter >> >>var1 >> > >> > >> > >> > >> > "Cor Ligthert" <no************@planet.nl> wrote in message >> > news:uj**************@TK2MSFTNGP11.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 >> >> >> >> >> > >> > >> >> > >
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Marc |
last post by:
Hello,
I have a form with multiple selects (pullDown). All selects have the
same name but each select has a different value. The user selects a
value in each select. When he submits the form I...
|
by: Peter A. Schott |
last post by:
I've got a file that seems to come across more like a dictionary from what I can
tell. Something like the following format:
###,1,val_1,2,val_2,3,val_3,5,val_5,10,val_10...
|
by: d.schulz81 |
last post by:
Holas,
I want to do the following:
If one entry in multiple select "domain" is selected, i want to change
the content in dropdown "typ" dynamically according to the selected
domain.
If more...
|
by: 42 |
last post by:
Hi,
Stupid question:
I keep bumping into the desire to create classes and properties with the
same name and the current favored naming conventions aren't
automatically differentiating them......
|
by: Gummy |
last post by:
Hello,
I created a user control that has a ListBox and a RadioButtonList (and other
stuff). The idea is that I put the user control on the ASPX page multiple
times and each user control will...
|
by: robert.waters |
last post by:
Hello,
I have been experiencing crashes and code corruption in my project
(vbe6.dll; a decompile fixes the corruption); for the life of me I
cannot figure out why, and I can't pin down the...
|
by: Zak |
last post by:
Hello,
I need some help trying to grab the data selected from a checkboxlist. The
checklist is acting like a menu. The first selection is accepted but not the
multiple ones. For example is the...
|
by: Alo2aaa |
last post by:
Dear all
i have two linked combo boxes (master/child) ,the user wants to have multiple selection from both combo boxes , can u tell me how can i store them in a way that i can have a unique id...
|
by: trullock |
last post by:
Hi,
Can anyone suggest the best way to go about the following...
I'm tracking clicks (mouse down x,y coordinates) on a web page by
using some javascript to create an XHR which sends the...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |