Sql Query

[Nomad]

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?

[neeraj.singh]

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.

[Nomad]

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

[masijade]

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.

[Nomad]

Your probably right but it was worth a shot.

[neeraj.singh]

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
/\/

[Nomad]

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 ? :)

[masijade]

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

[Nomad]

Well gee thanks for banishing me...

[masijade]

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?

[neeraj.singh]

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..

[masijade]

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?

[masijade]

Oh, damn it, here:

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.

[neeraj.singh]

Oh, damn it, here:

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

[masijade]

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.

[Nomad]

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

[masijade]

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.