working with more than one DB tables

Question

0.00/5 (No votes)

See more:

Hi,

I am working with something and I came up with a question..

I created a database with one table in it, in this table I have the primary key(autogenerated ID), name, surname,email etc...
The above are describing a customer. But say if I want more fields to describe one customer(not so important like weight, height, shape etc..) but I didn't want to put them in the above table, what can I do?

Can I create a second table? but when I modify one customer from the first table with the ID how can I access the correct data for this customer from the second table without ID?

hope you understand..
thanks.

Posted 29-Oct-13 21:50pm

zapata287

Add a Solution

Comments

Tom Marvolo Riddle 30-Oct-13 3:54am

use customerid(autogenerated) as common in both tables.Then use inner join for retrieving data

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-10-29T21:57:00

Yes, you can - it probably isn't necessary to create a separate table (since you can specify which fields you want to retrieve when you SELECT them):

SQL

SELECT name, surname FROM MyTable WHERE ID = 22

but if you do, then you set up a new table (with it's own ID column) and what is called a Foreign Key column which contains the ID value from the original table:

C#

ID      int, identity
CustID  int, foreign key
Weight  float
Height  float

You can then retrieve them both together like this:

SQL

SELECT a.name, a.surname, b.Weight, b.Height FROM MyTable a
JOIN MyOtherTable b ON a.ID=b.CustId
WHERE a.ID = 22

Prasad Khandekar · Accepted Answer · 2013-10-29T22:04:00

Hello,

What you need is to create the another table and set the customerid as the foreign key in that table. Below MySQL snippet should help you.

SQL

CREATE TABLE customer_mst (
    customerid MEDIUMINT NOT NULL AUTO_INCREMENT,
    firstname  VARCHAR(64),
    lastname   VARCHAR(64),
    emailed    VARCHAR(255),
    ...,
    PRIMARY KEY (customerid)
);

CREATE TABLE customer_info (
    infoid     MEDIUMINT NOT NULL AUTO_INCREMENT,
    customerid MEDIUMINT NOT NULL,
    weight     MEDIUMINT DEFAULT 0,  
    height     MEDIUMINT DEFAULT 0,
    ...,
    PRIMARY KEY (infoid),
    FOREIGN KEY (customerid) REFERENCES customer_mst(customerid)
)

Regards,

working with more than one DB tables

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0