Solved

complex sorting

Posted on 2003-11-26
4
211 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
Comment Utility
There is an end bracket missing that closes out the function, just in case you were wondering.
0
 
LVL 4

Expert Comment

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

Author Comment

by:WhatupE
Comment Utility
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
Comment Utility
SELECT Table1.Col1, Table1.Col2
FROM Table1, Table3
WHERE (Table1.Col1 = Table3.Col1)
AND (Table3.Col3 = X)
ORDER BY Table3.Col6
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Article by: Ivo
Anonymous Types in C# by Ivo Stoykov Anonymous Types are useful when  we do not need to follow usual work-flow -- creating object of some type, assign some read-only values and then doing something with them. Instead we can encapsulate this read…
This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now