Solved

This may be a chanlenge question

Posted on 2003-11-14
25
308 Views
Last Modified: 2010-03-31
Hello, everyone, I know you are all busy I have I think is a little complicated question but maybe somebody has done something like this before. What I want to do is in Java query a table that some information. Which is fine however. I want to output this to a file in a certain format that is requested. The problem I am having one of the key fields is not always the same and the other key field which is based on the first key field is not always the same either. I know this probably isn't making much sens but let me give you an example.
Let say I have a column that has the number 4 in for about 20 rows and then accross that is another column that may have 4 in it or also 3. Now there is more info in the row so what I want to do in java is select "" info that is related to number 4 and other coulumn 4 and output in a format. with out repeating the number 4 20 times. Then I need to output the info that is still related to the first coulmn 4 but now is related to the 3 instead of the for. So in other words I don't want to just read eact record and output it into the file. I need to to do it by bassing the relation ship on the coulumn that has 4 in it and the second coulmn that has 4 and 3. In other words I don't want reduntant coulmn 1 and 2 being repeating data. If anyone can help I will most happly hook you up with some points. Thanks for anyone that is willing to help.
0
Comment
Question by:Corey_819
  • 10
  • 8
  • 6
  • +1
25 Comments
 
LVL 35

Expert Comment

by:TimYates
ID: 9747361
I'm still confused...

Can you give a better example?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9747368
I'm not sure if i can follow all that, but this is one way of restricting the number of rows returned:

SELECT DISTINCT * WHERE key_col = 4
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9747397
Actually, i've got a dim memory that you can't use * with DISTINCT, so name the columns. It's always better to name the columns anyway in fact.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 15

Expert Comment

by:dualsoul
ID: 9747447
please post a sample. and also post the result you want to get from this sample.
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9747462
Distinct will only skip rows that ALL the fields are the same, so

...ID...|...ID2...|...DATA...
...1.....|...1.......|...Fish.....
...1.....|...1.......|...Fish.....
...1.....|...1.......|...Cheese.....

will come out as:

1, 1, Fish
1, 1, Cheese

Tim
0
 
LVL 1

Author Comment

by:Corey_819
ID: 9747487
I am sorry I know it sounds confusion it took me a few days to figure out what I was trying to do. Okay like let say I have a record it has a hold load of info in it. But in the first two colmns I have lets say data that looks like this:
Key 1                                           key 2
444444                                         444444   Data.........................
444444                                         444444   ""
444444                                         444444   ""
444444                                         444444
444444                                         666666
555555                                         555555
555555                                         555555
555555                                         444444
555555
Now that is what some what my table looks like with more information in the records. Now I need to select all that data and output it to a file in this format such as:
Key 1 * 444444
Key 2 * 444444
      The all the data related to key 1 and key 2 those keys and records then the next output set would be this
Key 2 * 666666
      Then all the data related to the key 2 66666 would be underneath and  then the same format output with the 555555 and the key 2.

So basically I am saying I can't do a select * and set a where to equal something because my keys are changing and there are about 8 different key 1 fields and 12 different key 2 fields. I mean I could set the were to only output on the key I put in the string but to me that is a lot of wasted writting code. Plus there are about over 20000 records I would need to find all the keys that are distinguisedly listed. Any ideas.

Let me know if you have any other questions I wish I could send you a better sample of  the data but it is very perosnal data. Sorry so I am just trying to give you an example of what needs to be done. Thanks for the help
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9747503
SELECT DISTINCT key1, key2 from yourtable ;

will give you all the available keypairs
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9747508
Actually,

 SELECT DISTINCT key1, key2 from yourtable ORDER BY key1, key2 ;

will give you a neater list...

in this example, you will get:

444444, 444444
444444, 666666
555555, 444444
555555, 555555

Tim
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9747524
You can then go through the returned resultset, and read each set of keys like:

SELECT data FROM yourtable WHERE key1=? AND key2=?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9747576
Ah, select distinct - that rings a bell ;-)
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9747594
Hee hee ;-)

Yeah...sorry about that ;-)

***Please note that CEHJ mentioned DISTINCT first, if that solves your problem!!***

(*cough* split *cough*) ;-)
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9747605
Some points to Tim for expanding it though .. :-)
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9747609
:-)
0
 
LVL 1

Author Comment

by:Corey_819
ID: 9747882
Okay guys I am going to try the distinct, however one quick question does the key fields  need to be set as primary keys or one of the fields need to be set as primary key? Thanks
0
 
LVL 1

Author Comment

by:Corey_819
ID: 9747916
Okay wait, I think I forgot to tell you all something. The way the table is set up I still need the record where key 1 and key 2 equal each other, I just don't need key 2 repeating its data inf the field if it is the same as the current or previous record. Would distinct still work?

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9747927
There's no requirement that either needs  to be a primary key or even that there needs to be an index on either. Performance would be better if there were indexes though.
0
 
LVL 1

Author Comment

by:Corey_819
ID: 9747947
Wait I just noticed that I did not completly explain the way the table is laid. There are never records where key 1 = key 2. however there are records where key 1 will have the sam value or data that the previous and current reocord has and key 2 is the same way. I am sorry this probably changes the solution
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9747984
Right this is a challenge as I haven't got a clue what's going on...

Do you have a complete example that is correct?
0
 
LVL 1

Author Comment

by:Corey_819
ID: 9748115
I am so sorry I got my thoughts twisted I have been just beathing my head trying to figure this out for about a week. I have just go twisted on what I didn't want to repeat. I really do apologize. Anyway if you are still willing to help I will provide the right example. This is what I need it too do::

key 1  key 2
444    333
444    333
444    222
555    111
555    111
555    111

Okay I need to display every single record in the data base I just don't need the key 1 and key 2 display the same data more then one time. Example
444
   333
       data in record every record that has 333
   222
       data in record for every 222.
555
   111
     all the records with 111
endof file.
This is what I need it to do I am sorry for the confusion I have been just running 5,000 thoughts threw my head last few days

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9748151
OK, you don't actually need DISTINCT. You can do the following and then iterate the result set. Try the query on its own first in your db console

select col1, col2 from sometable group by 1,2
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9748160
In fact, simply

select col1, col2 from sometable group by 1

should suffice
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 500 total points
ID: 9748253
The following should then produce the output your require

while (rs.next()) {
      String col1 = rs.getString("col1");
      out.println(col1); // 'out' is a  PrintWriter opened on a FileWriter
      while (rs.next()) {
            String col2 = rs.getString("col2");
            out.println("\t" + col2); // function to output to file
            String _col1 = rs.getString("col1");
            if (_col1.equals(col1) == false)
                  break;
      }
}
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9748256
The following should then produce the output your require

while (rs.next()) {
      String col1 = rs.getString("col1");
      out.println(col1); // 'out' is a  PrintWriter opened on a FileWriter
      while (rs.next()) {
            String col2 = rs.getString("col2");
            out.println("\t" + col2);
            String _col1 = rs.getString("col1");
            if (_col1.equals(col1) == false)
                  break;
      }
}
0
 
LVL 1

Author Comment

by:Corey_819
ID: 9748352
Okay that will give me a good idea on how to begin and what I need to do Thank you for all your help
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9748421
:-)

Sorry about the DISTINCT red herring ;-)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
The viewer will learn how to implement Singleton Design Pattern in Java.

822 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