Query help

1

Table:student

StudentID

A12341
A12342
A12343
A12344
A12345
A12346

Table:studentStat

StudentstatID

A12341
A12342
A12343
A12349
A12348
A12346

I want to compare the values of the student table values against studentstatID table, if the studentID exists in the studentstat table then return the values of student table with valid or invalid status.

Expected output for the above
Table:student

StudentID Status

A12341 Valid
A12342 Valid
A12343 Valid
A12344 Invalid
A12345 Invalid
A12346 Valid

Thanks for your help in advance !

2

http://www.sqlservercentral.com/blogs/spaghettidba/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

3

Joe's right, please try to put some sample SQL in and format a little nicer, but here's what I think you might be looking for:

SELECT S.StudentID, CASE WHEN SS.StudentstatID IS NULL THEN 'Invalid' ELSE 'Valid' END 'Status'
FROM student S LEFT OUTER JOIN studentStat SS ON S.StudentID = SS.StudentstatID
ORDER BY S.StudentID

LEFT OUTER JOIN takes all the values in the first table and attempts to match them to the second table, and returns NULL if there is no match. The CASE statement just turns NULL's into the string 'Invalid' otherwise the string is 'Valid'

4

This...

SELECT 
	s.StudentID,
	[Status] = CASE WHEN ss.StudentStatID IS NOT NULL THEN 'Valid' ELSE 'Invalid' END
FROM
	dbo.Stuent s
	LEFT JOIN dbo.StudentStat ss
		ON s.StudentID = ss.StudentStatID;