Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
the first sub-query return data repeatedly

SELECT     *
FROM          (SELECT     TOP (100) PERCENT dbo.subjects.Dsubject AS 'Dsubject1', dbo.RESULT.TOTAL AS 'TOTAL1', dbo.RESULT.SEMESTER AS 'SEMESTER1', dbo.RESULT.TotalCreditMark AS 'TotalCreditMark1', 
                                                   dbo.RESULT.MAKEUP1 AS 'MAKEUP11', dbo.RESULT.MAKEUP2 AS 'MAKEUP21', dbo.RESULT.MAKEUP3 AS 'MAKEUP31'
                            FROM          dbo.Student INNER JOIN
                                                   dbo.RESULT ON dbo.Student.Regno = dbo.RESULT.REGNO INNER JOIN
                                                   dbo.subjects ON dbo.RESULT.SUBJECTID = dbo.subjects.ID INNER JOIN
                                                   dbo.Sessions ON dbo.Student.Session = dbo.Sessions.ID 
                            WHERE      (dbo.RESULT.SEMESTER = '1st')) w OUTER apply
                          (SELECT     TOP (100) PERCENT dbo.subjects.Dsubject AS 'Dsubject2', dbo.RESULT.TOTAL AS 'TOTAL2', dbo.RESULT.SEMESTER AS 'SEMESTER2', dbo.RESULT.TotalCreditMark AS 'TotalCreditMark2', 
                                                   dbo.RESULT.MAKEUP1 AS 'MAKEUP12', dbo.RESULT.MAKEUP2 AS 'MAKEUP22', dbo.RESULT.MAKEUP3 AS 'MAKEUP32'
                            FROM          dbo.Student INNER JOIN
                                                   dbo.RESULT ON dbo.Student.Regno = dbo.RESULT.REGNO INNER JOIN
                                                   dbo.subjects ON dbo.RESULT.SUBJECTID = dbo.subjects.ID INNER JOIN
                                                   dbo.Sessions ON dbo.Student.Session = dbo.Sessions.ID 
                                                   
                            WHERE      (dbo.RESULT.SEMESTER = '2nd')) a a


What I have tried:

how to write sub query to have more value
Posted
Updated 13-Feb-22 0:53am
v2

OUTER APPLY work as LEFT OUTER JOIN on tables. In your query you have two different queries and there is no join between them. To match the records with second query you need to check on the basis on both result sets column. like second query WHERE clause:
WHERE (dbo.RESULT.SEMESTER = '2nd' AND dbo.Student.StudentId = w.StudentId)
For more information on Sub-Query, Read
Subqueries (SQL Server) - SQL Server | Microsoft Docs[^]
 
Share this answer
 
Your query does not make sense because it is showing a group of subject, semester missing studen info all togather.


i think the same could be acheived in a much simpler way.

Food for thought:

SQL
SELECT  TOP (100) PERCENT dbo.Student.Regno, dbo.subjects.Dsubject AS 'Dsubject1', dbo.RESULT.SEMESTER AS 'SEMESTER', Sum(dbo.RESULT.TOTAL) AS 'TOTAL',  Sum(dbo.RESULT.TotalCreditMark) AS 'TotalCreditMark', Sum(dbo.RESULT.MAKEUP1) AS 'MAKEUP1', Sum(dbo.RESULT.MAKEUP2) AS 'MAKEUP2', Sum(dbo.RESULT.MAKEUP3) AS 'MAKEUP3'
FROM    dbo.Student 
INNER JOIN  dbo.RESULT ON dbo.Student.Regno = dbo.RESULT.REGNO 
INNER JOIN  dbo.subjects ON dbo.RESULT.SUBJECTID = dbo.subjects.ID 
INNER JOIN  dbo.Sessions ON dbo.Student.Session = dbo.Sessions.ID 
group by dbo.Student.Regno, dbo.subjects.Dsubject, dbo.RESULT.SEMESTER
Having dbo.RESULT.SEMESTER in ('1st','2nd')
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900