7 Replies Latest reply: Mar 27, 2010 3:02 PM by 3004 RSS

    Database Query

    843789
      I am Quering a database with three tables, Users, UserStocks, Stocks which Should Return All Stocks Held by Each user, so far it does just that but not in the logic i need it to.
      This is what i want it to return:

      Admin01 Default Admin
      Stock01, Stock02, Stock03
      user01 Bill Buyout
      Stock 02, Stocks01
      user02 Fran Futures
      Stock02, Stock03


      what i have currently is a repetition of the userID whenever a Stock record is Found
      import java.io.*;
      import java.sql.*;
      import java.util.*;
      
      public class Query
      {
              public static void main(String[] args) throws IOException,ClassNotFoundException,SQLException
              {
      
      
                      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                      String url = "jdbc:odbc:StockTracker";
                      Connection con = DriverManager.getConnection(url);
                      Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
      
                      System.out.println("Stock holdings by User");
                      System.out.println("");
                      System.out.println("User ID User Name");
                      System.out.println("            Stock - Description");
                      System.out.println("--------------------------------------------");
      
      
      
                          ResultSet rs = stmt.executeQuery("SELECT * FROM Users u, UserStocks us, Stocks s WHERE  u.userID=us.userID AND us.symbol=s.symbol ORDER BY u.userID");
      
                          String CurrentUser = null;
                          String PrevUser =null;
      
                      while(rs.next())
                      {
                               CurrentUser = "userID";
                               System.out.println(rs.getString("userID")+"\t"+rs.getString("firstName")+"\t"
                                                                                      +rs.getString("lastName"));
                          if(PrevUser == null|| !PrevUser.equals(CurrentUser))
                          {
                                     System.out.println("\t"+rs.getString("symbol")+"\t"+rs.getString("name"));
                               }
      
                          }
      
                }
      }
        • 1. Re: Database Query
          3004
          Jacky_M wrote:
          I am Quering a database with three tables, Users, UserStocks, Stocks which Should Return All Stocks Held by Each user, so far it does just that but not in the logic i need it to.
          This is what i want it to return:

          Admin01 Default Admin
          Stock01, Stock02, Stock03
          user01 Bill Buyout
          Stock 02, Stocks01
          user02 Fran Futures
          Stock02, Stock03
          It's not clear what you want or what you're getting instead.

          If I understand correctly, you're getting what you want, but it's not printing out the way you want it to?

          If so, include the desired and actual printout inside code tags.
          • 2. Re: Database Query
            843789
            desired:
            Admin  Default  Admin
                       Stock01, Stock02, Stock03
            User01  Bill      Buyout
                       Stock02, Stock03
            User02  fran     Futures  
                       Stock02,Stock01
            What i'm getting now:
            Admin  Default  Admin    
                       Stock01
            Admin  Default  Admin
                       Stock02
            Admin  Default  Admin
                       Stock03
            User01  Bill     Buyout
                       Stock02
            user01  Bill     Buyout
                       Stock03
            user02  Fran  Futures
                       Stock02
                       Stock01
            • 3. Re: Database Query
              3004
              Jacky_M wrote:
              desired:
              Admin  Default  Admin
              Stock01, Stock02, Stock03
              User01  Bill      Buyout
              Stock02, Stock03
              User02  fran     Futures  
              Stock02,Stock01
              What i'm getting now:
              Admin  Default  Admin    
              Stock01
              Admin  Default  Admin
              Stock02
              Admin  Default  Admin
              Stock03
              User01  Bill     Buyout
              Stock02
              user01  Bill     Buyout
              Stock03
              user02  Fran  Futures
              Stock02
              Stock01
              Okay, so you're having a logic problem.
               while(rs.next())
                              {
                                       CurrentUser = "userID";
                                       System.out.println(rs.getString("userID")+"\t"+rs.getString("firstName")+"\t"
                                                                                              +rs.getString("lastName"));
                                  if(PrevUser == null|| !PrevUser.equals(CurrentUser))
                                  {
                                             System.out.println("\t"+rs.getString("symbol")+"\t"+rs.getString("name"));
                                       }
               
                                  }
               
                        }
              Can you tell me what part of the above code you expect to print the commas between the stocks?

              Can you tell me what part of the above code you expect to NOT print the user name every time?

              Can you tell me what CurrentUser is for, why it's initialized to "UserId", and why it's never changed?
              • 4. Re: Database Query
                843789
                ok, Stocks don't have to be separated by commas, they need to be tabbed rather meaning each one will print after another, like this:
                Stock01
                                                                                                                                                                                                                Stock02
                                                                                                                                                                                                                Stock03
                And for as long as
                u.userID=us.UserStocks
                all the stocks associated with that user needs to be printed out and uonly proceed to the next user, which is user01 in this case after the above has completed going forth to user02 and then listing the stocks.

                The currentUser, i was trying to achive the above
                • 5. Re: Database Query
                  3004
                  Jacky_M wrote:
                  ok, Stocks don't have to be separated by commas, they need to be tabbed rather meaning each one will print after another, like this:
                  Stock01
                  Stock02
                  Stock03
                  And for as long as
                  u.userID=us.UserStocks
                  all the stocks associated with that user needs to be printed out and uonly proceed to the next user, which is user01 in this case after the above has completed going forth to user02 and then listing the stocks.

                  The currentUser, i was trying to achive the above
                  So, setting CurrentUser to "userID" and then never changing it is supposed to achieve that? Care to explain how you thought that would work?

                  I think you need to go back to the drawing board.

                  You're basically getting this back from the DB, right?
                  user01 Joe stock01
                  user01 Joe stock02
                  user02 Bob stock01
                  user02 Bob stock03
                  user02 Bob stock04
                  And what you want is
                  user01 Joe
                      stock01, stock02
                  
                  user02 Bob
                      stock01, stock03, stock04
                  Right?

                  So write down in English, very precisely, exactly how you would do that. If you can't describe it precisely in English, you won't be able to write it in Java.

                  Edited by: jverd on Mar 27, 2010 12:33 PM
                  • 6. Re: Database Query
                    843789
                    well dat doesn't help me much but then i guess thats how you decided to assist, thanks. for the record i have gone through this a couple of times
                    • 7. Re: Database Query
                      3004
                      Jacky_M wrote:
                      well dat doesn't help me much
                      It would if you would be motivated enough to follow it. Programming is not just typing a bunch of computer words and hoping it works.
                      but then i guess thats how you decided to assist,
                      Yup. Because it's the right way.
                      thanks.
                      You're welcome.
                      for the record i have gone through this a couple of times
                      So go through it again. I can't tell you how many times I've had to rethink, re-plan, and rewrite my code.

                      Here's a start:
                      For each row
                        if the user id is the same as the last row
                          just add the stock number to the current line
                        otherwise
                          start a new line with the user id and name
                      I deliberately left out a thing or two, and some of those steps need to be further broken down into more detailed sub-steps (which, by the way, are good candidates for their own methods).