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
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.
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.
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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.