Datamart - Table type

I am working with DatMart with out any tool.I have table where its lists student details.

SystudentID Campus   FirstNAme  LastName
   8          AICASA    Ali       Mehdi
   9          AICASA    Ashka      Patel
  10          AICASA    Jane       Iwuchukwu-Brooks
  11          AICASA    Helen      Oredeko

A student can enroll into multiple program as below.(It is not fixed)

AdEnrollID  SyStudentId Program Term
2671    9   AICASA  Fall - 2009 AICASA                 
2537    11  AICASA  Spring - 2009 AICASA               
2634    8   AICASA  Fall - 2010 AICASA                 
1853    10  AICASA  Fall - 2008 AICASA

Now I am combining these two tables and I want to make one table REP_student where all details I want . But it shouldn't give me two row. (Please see the table structure) IS there any Table type in SQL . What is the best way to achieve it?

SystudentID Campus  FirstNAme   LastName AdEnrollID SyStudentId Program Term

Thanks

Solution

You can use a SQL join for this. Here I am assuming you want to display only the records where table 1 has a related record in table 2 hence using INNER JOIN.If you want a different result you can try out LEFT JOIN ,RIGHT JOIN OR FULL OUTER JOIN according to your needs.

SELECT S.SystudentID ,S.Campus,S.FirstNAme,S.LastName,C.AdEnrollID,C.SyStudentId,C.Program,C.Term 
FROM table1_name S
INNER JOIN table2_name C
ON S.SystudentID  = C.SystudentID;

If you want a whole new table, I suggest to create a new table separately and use this in an INSERT query to insert your two table's existing values.

INSERT INTO new_table_name(SystudentID,Campus,FirstNAme,LastName,AdEnrollID,New_SyStudentId,Program,Term)
SELECT S.SystudentID ,S.Campus,S.FirstNAme,S.LastName,C.AdEnrollID,C.SyStudentId,C.Program,C.Term 
FROM table1_name S
INNER JOIN table2_name C
ON S.SystudentID  = C.SystudentID;

You may also need to consider removing one of the SyStudentId from the new table and the query since it is most probably a duplicate value.Here I just included it since you have mentioned you need it in the output table.