How to create a single table using same columns of 3 tables.

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I have 3 tables of Admin, Customer and designer.
Each table has comman columns named Email n password

I want to create a single table using these 2 columns.
So that i can have Email n password of Admin, Customer n Designer in a single table.

how can i do it??
Plz tell me.

Posted 5-Apr-12 22:19pm

Chitra_105

Add a Solution

4 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 · Answer 1 · 2012-04-05T22:37:00

This is a little complex, and I can't really answer it specifically.
The reason is that it depends on a number of factors:
If you create an Info table which holds the email and password info there are two ways you can relate it back to the original table: 1) You can have an InfoId field in the Admin table, and an InfoId in the Customer table, and an InfoId in the Designer table, each of which refer to a row in the Info table, and which could all refer to the same row if necessary. So if you change the password for via the Admin table reference to the InfoId, it will automatically change it for the Customer and Designer.
2) You can have a UserID field in the Info table which refers back to the appropriate Admin, Customer or Designer table. For this to work, you either need ids which are unique across the system (so you can tell which table it came from) or an additional field in the Info table which says "this is an admin id".

Me? I'd probably go with the first one - but it will depend on how your system is supposed to work.

BTW: You aren't storing those passwords in clear text, I hope? You are hashing them, right?

Om Prakash Pant · Answer 2 · 2012-04-05T22:38:00

Solution 3

Assuming data is same in all these tables for email and password, you can create new table by using the following SQL:

SQL

SELECT email, password INTO new_table_name from existing_table

Check the following link for examples & more details:
http://www.w3schools.com/sql/sql_select_into.asp[^]

Posted 5-Apr-12 22:38pm

Om Prakash Pant

Reza Ahmadi · Answer 3 · 2012-04-05T22:43:00

Hi,
The solution is to create a table which has 4 columns: Id, Email, Password, PersonId and then you need to create a relation between 3 tables and the new table. I mean id of the 3 tables must be a foreign key of the new table:
New table has four fields:

Id --Primary key of the new table
Password
Email
PersonId -- which is a foreign key from the 3 tables

I hope it will help,
Cheers

Bala Selvanayagam · Answer 4 · 2012-04-06T23:00:00

Assume that the three tables (Admin, Customer and designer) are being used by an existing application with data and for simplicity you wants to combine them into one so that your queries are simple to write ?

You may create a database VIEW combining three tables with an additional run time field to denote from which table the data is comming from. This way you can use your VIEW in your query and any data updated on the underlining tables (Admin, Customer and designer) are immediatly available in the VIEW.

The next question you may have is how do i insert / update data ?

You can use an "INSTEAD OF TRIGGER" on the "VIEW" so that any insert / update statement on the "VIEW" can be redirected to the appropriate table - This feature is available on SQL SERVER 2005 and later versions only

Hope this helps

How to create a single table using same columns of 3 tables.

4 solutions

Solution 2

Solution 3

Solution 4

Solution 5

Add your solution here

Preview 0