Geek asks and answers

Sql Query: cannot combine data from different tables

I cannot resolve this request.

Tables:

tblStandard1students
tblStandard2students
tblStandard3students     
tblCandidateinfo

tblStandard1students, tblStandard2students and tblStandard3students tbl contain information about students participating in standards 1,2 and 3.

tblStandars1students

Candid  admitted
  1        Y
  2        N
  3        Y


tblCandidateinfo

Candid  gender Division
  1       M      1
  2       F      2

etc.

Now I want the table to look like

Gender  Students(Standard1)  Students(Standard2)  Students(Standard3)
------------------------------------------------------------------------
 Male           10                 20                      30    
 Female         10                 30                      40

I tried this, but this does not give me an error:

SELECT case when Gender='M' then 'Male' 
            when Gender='F' then 'Female' 
       END AS Gender,

( SELECT count(*)
 FROM tblStandard1students A
 where A.Candid=B.Candid
 ) AS Students(Standard1),

( SELECT count(*)
 FROM tblStandard2students A
    where A.Candid=B.Candid
) AS Students(Standard2),

( SELECT count(*)
 FROM tblStandard3students A
    where A.Candid=B.Candid
) AS Students(Standard3)


FROM tblCandidateinfo B
group by Gender
+5
source share
3 answers

SQLFiddle demo

select 
case when Gender='M' then 'Male' 
            when Gender='F' then 'Female' 
      END AS Gender,
sum(T.std1) as [Students(Standard1)],
sum(T.std2) as [Students(Standard2)],
sum(T.std3) as [Students(Standard3)]
from
tblCandidateinfo as C

JOIN
(
select Candid, 1 as std1, 0 as std2, 0 as std3 
from tblStandars1students
union all
select Candid, 0 as std1, 1 as std2, 0 as std3 
from tblStandars2students
union all
select Candid, 0 as std1, 0 as std2, 1 as std3 
from tblStandars3students
) as T on (C.Candid=T.Candid)

GROUP BY GENDER
+1
source

The query syntax applies to Oracle. Basically, I used the right outer join.

select case when Gender='M' then 'Male' 
            when Gender='F' then 'Female' END AS Gender,
       count(A.candid) AS "Students(Standard1)",
       ount(A1.candid) AS "Students(Standard2)",
       ount(A2.candid) AS "Students(Standard3)" 
from tblStandard1students A,
     tblStandard2students A1,
     tblStandard3students A2,
     tblCandidateinfo B
where A.candid(+)=B.candid and A1.candid(+)=B.candid and A2.candid(+)=B.candid
group by gender
+1
source

PIVOT

SELECT CASE WHEN Gender='M' then 'Male' 
            WHEN Gender='F' then 'Female' 
       END AS Gender, [Students(Standard1)], [Students(Standard2)], [Students(Standard3)]
FROM (
      SELECT (SELECT gender FROM tblCandidateinfo i WHERE x.Candid = i.Candid) AS gender, List
      FROM (
            SELECT Candid, 'Students(Standard1)' AS List
            FROM dbo.tblStandard1students
            UNION ALL
            SELECT Candid, 'Students(Standard2)'
            FROM dbo.tblStandard2students
            UNION ALL
            SELECT Candid, 'Students(Standard3)'
            FROM dbo.tblStandard3students
            ) x
      ) x2
PIVOT 
 (
  COUNT(List) FOR List IN ([Students(Standard1)], [Students(Standard2)], [Students(Standard3)])
  ) p

SQLFiddle

0

More articles:


All Articles