SQL Server 2008 joining

Question

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.

Answer 1

You should consider to work with UNION rather then a JOIN.

Simplifying your query the union might look like this

SELECT           dbo.Stu_info.Id_num, dbo.Development_fee.Dvf
FROM             dbo.Stu_info 
LEFT OUTER JOIN  dbo.Tuition_fee ON dbo.Stu_info.Id_num = dbo.Tuition_fee.Id_num 
UNION
SELECT           dbo.Stu_info.Id_num, dbo.Registration_fee.Reg_fee
FROM             dbo.Stu_info 
LEFT OUTER JOIN  dbo.Registration_fee ON dbo.Stu_info.Id_num = dbo.Registration_fee.Id_num