combine two tables in sql

Question

By using Sql server 2008 I can return two table results like

College    Department   Course    Section   Passed

  X           CS         B.Sc        A        30
  X           CS         B.Sc        B        12

and the second result

College    Department   Course    Section   Failed

  X           CS         B.Sc        A        23
  X           CS         B.Sc        B        42

here, am joining one extra table than the first one, if I use the same query to get both the passed and failed count, the count goes wrong, thats what am using two different queries.

Am trying to combine these two tables, to get the result like

College    Department   Course    Section   Passed   Failed

  X           CS         B.Sc        A        30       23
  X           CS         B.Sc        B        12       42

but I dunno how to do this, can anyone help me out here, thanks in advance.

Note : here am joining about 3 to 5 tables in both the queries.

Answer 1

You can use inner join for your required output.

For Example,

Select emp.name,emp.salary,Q.qualification from employee emp inner join qualification  Q.empid=emp.empid

For detail refer

https://www.w3schools.com/sql/sql_join_inner.asp/

https://avtartime.com/sql-inner-join-with-examples/

Answer 2

Use case Statement

select College,    Department ,  Course  ,  Section ,
       sum(case when <pass condition> then 1 else 0) as Passed  ,
       sum(case when <fail condition> then 1 else 0) as Failed
from <table1>
join <table2>
on (condition)
group by College,    Department ,  Course  ,  Section

Answer 3

select a.*b.failed
from ([first result query]) a
INNER JOIN ([second result query]) b on a.college=b.college and a.Department=b.Department and a.Course=b.course and a.Section=b.Section