Solved

complex sorting

Posted on 2003-11-26
4
216 Views
Last Modified: 2010-04-16
I have 3 tables:

Table1: Col1, Col2

Table2: Col3, Col4,

Table3: Col5, Col1, Col3, Col6

Where There is a 1-to-1 relation between Col1 and Col7, and another relation between Col4 and Col8

I want: Select Col1, Col2 From Table1 Where Col1 In (Select Col1 from Table3 Where Col4 = x)

And I want the results to be sorted by Col 9.

I can't do this with one query!  And if I use two queries, I have to open two readers, which interfere with each other.  With the two queries, my function is:

...
ArrayList al2
...
func1(){
   
   myConnection.Open();
   TestObj o2 = (TestObj)al2[comboBox1.SelectedIndex];
   string strQry ="Select Col1 From Table3 Where Col3 = x Order By Col6

   System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand(strQry, myConnection)
   System.Data.OleDb.OleDbDataReader myReader = myCommand.ExecuteReader();

   myReader.Read();
   bool stay = true;
   strQry2 = "Select TestID, TestName from Test Where TestID =" +Convert.ToInt16(myReader[0]);
   System.Data.OleDb.OleDbCommand myCommand2 = new System.Data.OleDb.OleDbCommand(strQry2, myConnection)
   System.Data.OleDb.OleDbDataReader myReader2 = myCommand2.ExecuteReader();

    while(stay){
        myReader2.Read();
        TestObj obj1 = new TestObj(Convert.ToInt16(myReader2[0]), Convert.ToString(myReader2[1]));
        al3.Add(obj1);
       
        if(!myReader.Read)
        {
           stay = false;
        }
        myReader2 = myCommand2.ExecuteReader();
    }

This doesn't work.  I get an error that says 1 reader must be closed before another is used.    If I close the reader, I can't use it again.

The long and short of it is, if I use two queries, I need to readers that interfere with each other.  And I don't know of a way to write one query.  Any help is greatly appreciated!
0
Comment
Question by:WhatupE
  • 2
4 Comments
 

Author Comment

by:WhatupE
ID: 9828946
There is an end bracket missing that closes out the function, just in case you were wondering.
0
 
LVL 4

Expert Comment

by:Tonylmiller
ID: 9829280
I don't see Col7, Col8, or Col9 in your tables. . .
0
 

Author Comment

by:WhatupE
ID: 9829338
Those three lines should read:

Where There is a 1-to-1 relation between Col1(Table 1) and Co1(Table 3), and another relation between Col3 (Table 2) and Col3 (Table 3)

I want: Select Col1, Col2 From Table1 Where Col1 In (Select Col1 from Table3 Where Col3 = x)

And I want the results to be sorted by Col6.
0
 
LVL 20

Accepted Solution

by:
TheAvenger earned 200 total points
ID: 9830177
SELECT Table1.Col1, Table1.Col2
FROM Table1, Table3
WHERE (Table1.Col1 = Table3.Col1)
AND (Table3.Col3 = X)
ORDER BY Table3.Col6
0

Featured Post

ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question