?
Solved

complex sorting

Posted on 2003-11-26
4
Medium Priority
?
225 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 800 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

770 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