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(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

 

[Guest]

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]

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]

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

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]

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]]

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]

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

 

[Cor Ligthert]

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 Proost]

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:

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

 

[Jay B. Harlow [MVP - Outlook]]

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]

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]]

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]

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