Solved

This may be a chanlenge question

Posted on 2003-11-14
25
304 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

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

For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…

747 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

12 Experts available now in Live!

Get 1:1 Help Now