SQL sub query in SQL Server

Question

The first table is pre and the second is tran. I want the S_SSN from tran where no and Code of transcript matches with Code and no in pre

The result I get from the Inner query should all be there, if all the requirements match. Then only I should take it. — user1989
– user1989, Commented Mar 11, 2015 at 3:19
Student 415671238 has D_Code = INFS and C_no = 600 also. — Felix Pamittan
– Felix Pamittan, Commented Mar 11, 2015 at 3:26
@wewesthemenace But that student does not have MATH 321 and INFS 501, as I mentioned — user1989
– user1989, Commented Mar 11, 2015 at 3:35

Felix Pamittan · Accepted Answer · 2015-03-11 03:35:20Z

1

Is this what you want?

;WITH Cte AS(
    SELECT
        t.Student_SSN,
        cc = COUNT(t.Student_SSN)
    FROM transcript t
    INNER JOIN prereq p
        ON t.C_no = p.P_no
        AND t.D_Code = p.P_Code
    WHERE
        p.D_Code = 'INFS' 
        AND p.C_no = 614
    GROUP BY t.Student_SSN
)
SELECT DISTINCT Student_SSN
FROM Cte
WHERE cc = (SELECT COUNT(*)
            FROM prereq  p
            WHERE p.D_Code = 'INFS' AND p.C_no = 614)

edited Mar 11, 2015 at 3:35

answered Mar 11, 2015 at 2:54

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user1989 Over a year ago

This does not give me the appropriate results.

Felix Pamittan Over a year ago

Just add the appropriate filter.

user1989 Over a year ago

The result I get from the Inner query should all be there, if all the requirements match. Then only I should take it.

user1989 Over a year ago

Yes, I get the result, thanks. But is it possible to get the result without using count ?

user1989 Over a year ago

Let us continue this discussion in chat.

Collectives™ on Stack Overflow

SQL sub query in SQL Server

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related