Results 1 to 17 of 17

Thread: Sql Query

  1. #1
    Nomad is offline Member
    Join Date
    Nov 2008
    Posts
    15
    Rep Power
    0

    Default Sql Query

    Hi,

    I'm working on a client management system.
    in my database i have a customer table a product table so if the customer clicks details he gets the customer table and if he clicks on last bought products for example he gets the product table

    What is a better design to have a long table containing both the customer and product details and get one part at a time or to have two tables and use two sql queries to get the data?

  2. #2
    neeraj.singh is offline Member
    Join Date
    Feb 2009
    Location
    Delhi
    Posts
    63
    Rep Power
    0

    Default

    you can manipulate both tables together
    like
    customer table will contain all the columns of the product table and every row will have all the details about customer..
    this is possible but not a good way to use database.
    you will increase processing time also.

  3. #3
    Nomad is offline Member
    Join Date
    Nov 2008
    Posts
    15
    Rep Power
    0

    Default

    Any more info on that, can you elaborate on your answer a bit?

  4. #4
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    I'm sorry, but this is a Java Forum. The dtabase header implies JDBC, i.e. Connection, DriverManager, DatabaseMetaData, Statement PreparedStatement, CallableStatement, ResultSet ResultSetMetaData, etc. That question is a pure SQL question, you would get better help at an actual SQL forum.

  5. #5
    Nomad is offline Member
    Join Date
    Nov 2008
    Posts
    15
    Rep Power
    0

    Default

    Your probably right but it was worth a shot.

  6. #6
    neeraj.singh is offline Member
    Join Date
    Feb 2009
    Location
    Delhi
    Posts
    63
    Rep Power
    0

    Default

    Quote Originally Posted by Nomad View Post
    Any more info on that, can you elaborate on your answer a bit?
    hi nomad

    suppose u hv two tables
    customer - carries info about customer, would hv some id(PK)
    and product - carries product info of a perticular customer with their PK.

    you can create a single table
    say, cust-pro
    which will hav all the column of both tables
    like
    cust_id, cust_name, prod_id, prod_name
    now you can get the records with cust id.

    i think you are doing
    select * from product where cust_id = some_id;

    now you can do
    select * from cust-pro where cust_id = some_id;
    you will get all the info about the product and customers

    thanks
    /\/

  7. #7
    Nomad is offline Member
    Join Date
    Nov 2008
    Posts
    15
    Rep Power
    0

    Default

    So basically you went for the idea of having one large table containing all the data from both tables and use sql queries to get the pieces of info i require,why is that a bad way to use a database?
    it just seems very ineffective requiring multiple queries for every detail almost and also seems kinda strange logic to fill one have of the table then find it again later and fill the other there must be a simpler solution or am i crazy ? :)

  8. #8
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    It's called "join". Like I said, go to an SQL forum.

  9. #9
    Nomad is offline Member
    Join Date
    Nov 2008
    Posts
    15
    Rep Power
    0

    Default

    Well gee thanks for banishing me...

  10. #10
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    I'm not banishing you. I'm trying to help you. The advice you've gotten here, so far, has not been good. I could, possibly, tell you enough to get you through this problem, but that doesn't mean it would be the best advice. You will get much better help at an SQL forum.

    Do you expect a nurse to be able to tell you what plants to grow, and how to process them in order to come up with the cloth that makes a cast? They use casts, so they must know how to make them, right? No, you don't think that. So why do you think you'll get good SQL advice at a Java forum? We use DBs, so we must be experts in them, right?

  11. #11
    neeraj.singh is offline Member
    Join Date
    Feb 2009
    Location
    Delhi
    Posts
    63
    Rep Power
    0

    Default

    Quote Originally Posted by masijade View Post
    I'm not banishing you. I'm trying to help you. The advice you've gotten here, so far, has not been good. I could, possibly, tell you enough to get you through this problem, but that doesn't mean it would be the best advice. You will get much better help at an SQL forum.

    Do you expect a nurse to be able to tell you what plants to grow, and how to process them in order to come up with the cloth that makes a cast? They use casts, so they must know how to make them, right? No, you don't think that. So why do you think you'll get good SQL advice at a Java forum? We use DBs, so we must be experts in them, right?
    he just want to pack up with one single table.. so no join will work in such case..

  12. #12
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Quote Originally Posted by neeraj.singh View Post
    he just want to pack up with one single table.. so no join will work in such case..
    Well, @OP, like I said, the advice you've been getting here hasn't been very good.

    Retreiving info from two (or more) tables is exactly what join is for.

    P.S. You do know that the "(+)" in queries is a join, right?
    Last edited by masijade; 02-20-2009 at 09:53 AM.

  13. #13
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Oh, damn it, here:

    Java Code:
    Customer_Table
    ---------------
    Customer_Id
    Further_Fields (Such as address, name, billing info, etc)
    
    Product_Table
    --------------
    Product_Id
    Further_Fields (Such as product name, cost, model, description, etc)
    
    Purchases
    ----------
    Customer_Id -- Foreign_Key
    Product_Id -- Foreign_Key
    Further_Info (such as date and amount purchased, etc.)
    
    Select Cust.Customer_Id, Cust.Further_Fields, Prod.Product_Id, Prod.Further_Fields, Buy.Further_Info
    From Customer_Table Cust,
         Product_Table Prod,
         Purchases Buy
    Where Cust.Customer_Id = whatever
      And Buy.Customer_Id = Cust.Customer_Id
      And Prod.Product_Id = Buy.Product_Id
    Edit: BTW, those equals where clauses (especially when combined with the selection fields above) perform an implicit (and I say implicit because you are not using the "command" join in the query, but it is, actually rather explicit) join.

    As I said, I could probably help you through this one, but the right place to ask this question is on an SQL forum. Just some people here felt the need to argue, so they needed proving wrong.
    Last edited by masijade; 02-20-2009 at 10:08 AM.

  14. #14
    neeraj.singh is offline Member
    Join Date
    Feb 2009
    Location
    Delhi
    Posts
    63
    Rep Power
    0

    Default

    Quote Originally Posted by masijade View Post
    Oh, damn it, here:

    Java Code:
    Customer_Table
    ---------------
    Customer_Id
    Further_Fields (Such as address, name, billing info, etc)
    
    Product_Table
    --------------
    Product_Id
    Further_Fields (Such as product name, cost, model, description, etc)
    
    Purchases
    ----------
    Customer_Id -- Foreign_Key
    Product_Id -- Foreign_Key
    Further_Info (such as date and amount purchased, etc.)
    
    Select Cust.Customer_Id, Cust.Further_Fields, Prod.Product_Id, Prod.Further_Fields, Buy.Further_Info
    From Customer_Table Cust,
         Product_Table Prod,
         Purchases Buy
    Where Cust.Customer_Id = whatever
      And Buy.Customer_Id = Cust.Customer_Id
      And Prod.Product_Id = Buy.Product_Id
    Edit: BTW, those equals where clauses (especially when combined with the selection fields above) perform an implicit (and I say implicit because you are not using the "command" join in the query, but it is, actually rather explicit) join.

    As I said, I could probably help you through this one, but the right place to ask this question is on an SQL forum. Just some people here felt the need to argue, so they needed proving wrong.
    oh dear...
    this is the thing he already has..
    he does not want different table for customer and product..
    please read his query carefully

  15. #15
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Quote Originally Posted by neeraj.singh View Post
    oh dear...
    this is the thing he already has..
    he does not want different table for customer and product..
    please read his query carefully
    You need to read it carefully. He asked whether it was better to have multiple tables and queries or one large one and one query. And it is always better to have multiple tables when information will have to be repeated by having it in a single table, which is the case here.

    Which makes the answer 50/50, as it is best to have multiple tables, but still use one query. Which, once again, is an SQL question and has nothing to do with Java.

  16. #16
    Nomad is offline Member
    Join Date
    Nov 2008
    Posts
    15
    Rep Power
    0

    Default

    Thanks masijade exacly what i needed!
    if only you had done that earlier...

  17. #17
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    If only you would go to the right place with your questions.

    Edit: This question would have been answered inside of two hours at the most on an SQL forum.
    Last edited by masijade; 02-20-2009 at 02:06 PM.

Similar Threads

  1. I need help with a query.
    By Daredemo in forum JDBC
    Replies: 1
    Last Post: 08-13-2008, 05:16 AM
  2. Help in Query
    By geeta_ravikanti in forum JDBC
    Replies: 0
    Last Post: 03-31-2008, 01:16 PM
  3. Using sql:query tag
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 01-15-2008, 03:13 PM
  4. Using sql:query tag
    By Java Tip in forum Java Tip
    Replies: 0
    Last Post: 01-14-2008, 09:31 AM
  5. An interface query !!
    By ajaygargnsit in forum New To Java
    Replies: 3
    Last Post: 12-22-2007, 05:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •