Multiple join 1

[radubalmus]

Hy i am tring to join 2 querys but i don't get any results
and running them apart takes for ages

I have two table and i have only one commun column "testorder"

the code looks like this

            For testcounter = 2 To test.count + 1
                singletest = test.Item(testcounter - 1)
                For catcounter = 2 To cat.count + 1
                    category = cat.Item(catcounter - 1)
                    
                    query = "SELECT testorder,product_rating FROM test_orders WHERE " + datetale + " AND costcat ='" + category + "'" + " AND status='Closed' ORDER BY testorder"
                    Set MyRecordset = Module2.selectFromDB(query) ' connecting to the database
                    
                    Do Until MyRecordset.EOF
                    testorderCollection.Add (MyRecordset.Fields(0).Value)
                    ratingCollection.Add (MyRecordset.Fields(1).Value)
                    MyRecordset.MoveNext
                    Loop
                    Set MyRecordset = Nothing
                    Set MyConnection = Nothing

                    For count = 1 To testorderCollection.count
                        testorder = testorderCollection(count)
                        rating = ratingCollection(count)
                        If rating <> "Not Aplicable" Then
                            query = "SELECT result1,result2,result3,result4,result5,result6,result7,result8,result9,result10 FROM test WHERE testorder='" + testorder + "' AND test='" + singletest + "'"
                            Set MyRecordset = Module2.selectFromDB(query)
                            Do Until MyRecordset.EOF
                                
                                ' some code here

                                MyRecordset.MoveNext
                            Loop
                            
                            Set MyRecordset = Nothing
                            Set MyConnection = Nothing
                           
                            
                        End If
                    Next
                                                            
                Next 'Inner
            Next 'Outer

any ideeas??


There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[bborissov]

Where you JOIN these two queries?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[radubalmus]

hy bborissov

what do you mean exacly??

i want the "product_rating" from test_orders table for a certain testorder and "result1,...." from the test table for the same testorder


There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[bborissov]

But here you didn't JOIN the queries you just use the results from one to build other.
Nobody could tell you WHY the second query didn't work because nobody know you database.

Run SQL Profiler and check what queries you send to SQL Sever.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[radubalmus]

Maybe i didn't explained my self better

it is just lile you say i use the result from the first to build the second..
but it is taking to long to see the results

soo i would like to use an sql join for combining the two querys into one query(i think this will be much faster)..

and so i start to learn about join but i didn't manage to do somthing that worked; that's why i posted here, to get new directions


There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[bborissov]

SELECT result1,
       result2,
       result3,
       result4,
       result5,
       result6,
       result7,
       result8,
       result9,
       result10 
FROM test
INNER JOIN test_orders
      ON test_orders.testorder      = Test.testorder
WHERE test.test = '" + singletest + "'"

Not sure at all if this will work. Also I don't know from where singletest comes from.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[radubalmus]

Thanks bborissov i 'll give it a try..
i have one question )


Can i put in the WHERE a criteria from test and a criteria from test_orders??

ex. WHERE test.test = '" + singletest + "' AND test_orders.costcategory='" + category + '"

There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[radubalmus]

it is working
thanks

There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[bborissov]

Yes you could.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP