I have some tables in my database & I want to combine them by using join but I have got a problem. Let me make it clear. I have assigned Id_num (column name) as primary key in the Stu_info table & the other tables have an Id_num column to store ID number.
All tables have same name (Id_num) of that column which will store ID number. I have tried to make a view to connect tables to get specific data from specific tables. If I use INNER JOIN then it doesn't show any rows because of some null vales. However I have used LEFT OUTER JOIN it is working. But it makes a problem which is not acceptable.
Code :
SELECT
dbo.Stu_info.Id_num, dbo.Development_fee.Dvf, dbo.Stu_info.Stu_name,
dbo.Stu_info.Dep_name, dbo.Tuition_fee.Acy, dbo.Tuition_fee.Tui_fee,
dbo.Registration_fee.Reg_fee, dbo.Form_fill_up_fee.Acy AS Expr1,
dbo.Form_fill_up_fee.FFF, dbo.Examination_fee.E_typ, dbo.Examination_fee.Exm_fee,
dbo.monthly_instal.Instm, dbo.monthly_instal.Paid
FROM
dbo.Stu_info
LEFT OUTER JOIN
dbo.Tuition_fee ON dbo.Stu_info.Id_num = dbo.Tuition_fee.Id_num
LEFT OUTER JOIN
dbo.Registration_fee ON dbo.Stu_info.Id_num = dbo.Registration_fee.Id_num
LEFT OUTER JOIN
dbo.Examination_fee ON dbo.Stu_info.Id_num = dbo.Examination_fee.Id_num
LEFT OUTER JOIN
dbo.monthly_instal ON dbo.Stu_info.Id_num = dbo.monthly_instal.Id_num
LEFT OUTER JOIN
dbo.Development_fee ON dbo.Stu_info.Id_num = dbo.Development_fee.Id_num
LEFT OUTER JOIN
dbo.Form_fill_up_fee ON dbo.Stu_info.Id_num = dbo.Form_fill_up_fee.Id_num
For example Dvf column of Development_fee table has one data & Tui_fee column of Tuition_fee table has six data for a student who has following ID LAB10161117.
Now if you try to use following code
Select * from View_1
Where Id_num = 'LAB10161117'
to get data for following ID LAB10161117 then you suppose to get following information. Like
ID number : LAB10161117
-----------------------------------------
Development Fee ---- Tuition Fee ---- Other columns
10000 ---- 1000
Null ---- 1000
Null ---- 1000
Null ---- 1000
Null ---- 1000
Null ---- 1000
Because Dvf column of Development_fee table has one entry for LAB10161117 which is 10000 & Tui_fee column of Tuition_fee table has six entries for LAB10161117 which are 1000, 1000, 1000, 1000, 1000, 1000. But it doesn't show like above information. When I execute following code to show data for that ID
Select * from View_1
Where Id_num = 'LAB10161117'
then it shows wrong information like
ID number : LAB10161117
-----------------------------------------
Development Fee ---- Tuition Fee ---- Other columns
10000 ---- 1000
10000 ---- 1000
10000 ---- 1000
10000 ---- 1000
10000 ---- 1000
10000 ---- 1000
which is not right! It means to match with Tuition_fee table which has six rows it has been generated same value of Development_fee table again & again (six times) though Development_fee table has only one row for that person who has following ID LAB10161117.
All I want to show row exactly what is present there in table & I want to stop row duplication. Would you please help me to solve this problem? Please help me to get rid of this problem. Thank you.
