help. i would like to ask if there's a way to combine 2 data table into 1 table without manually inputting using sql statement.

how does the parent child foreign key works?

it goes like this:
student - table1 : lname, fname, age
teacher - table2 : lname, fname, age

it will be merged into classroom - table3

Edited 2 Years Ago by Gen_2

What is the layout of the new table? There is always a way to combine tables. The trick is to combine them in a way that makes sense. If you just want a table of people regardless of whether the people are students or faculty then you could

SELECT * FROM table1 
UNION 
SELECT * FROM table2

If you want to include a field that distinguishes between faculty and students you could do

SELECT 'S' AS classID,* FROM table1
UNION
SELECT 'F' AS classID,* FROM table2

which will return the fields classID, lname, fname and age where classID=S indicates a student and classID=F indicates faculty. If you want to insert the records inito a new table then add

INSERT INTO table3

at the start of the query.

Edited 2 Years Ago by Reverend Jim

the student table is : studid, lname, fname, gender, age
the teacher table is : facid, lname, fname, gender, age, dept, subject
the classroom table is : no, lname, fname

i just want to get all the names and when i need to modify it i don't need to go to student table nor teacher table. the classroom table will take the modification. that's really what i wanted to do.

I presume no is classNo. This doesn't appear in either of the two tables so you would have to generate it in the query as in

INSERT INTO classroom
SELECT 1 AS classNO, lname, fname FROM table1 
UNION 
SELECT 1 AS classNO, lname, fname FROM table2

However, the new table will give you no way to determine who is a student and who is faculty.

Edited 2 Years Ago by Reverend Jim

Hi,

If you are using a single dataset to hold the two tables you can also specify a DataRelation between them.

You can then get the child records of the parent record and vice versa.

Again you will still need to specify a parent primary key to child foreign key in your relationship though.

Edited 2 Years Ago by G_Waddell

This question has already been answered. Start a new discussion instead.