SQL return true if criteria match

Question

Hello.

I have an assignment table in my database. I also have a assignment_notes table, which has a reference to the assignment table. There can exists multiple rows in this table.

When I select all my assignments, I want to check if there exists some notes to this assignment. And all I want is a true/false return.

Is it possible to do something like (pseudo):

Select all assignments; if assignment has assignment_notes HasNotes = true; else HasNotes = false.

I hope I made this clear enough - I'm not so good at explaining programming stuff ;-)

Answer 1

You can translate your pseudo code to SQL if you use the case statement. On MSDN: http://msdn.microsoft.com/en-us/library/ms181765.aspx

And another article just in case: http://www.devx.com/tips/Tip/15633

Answer 2

Don't have SQL Server ready to test, but a query like this should work:

SELECT A.*, 
  CAST(
    CASE (SELECT TOP 1 AssignmentNotes_ID 
          FROM AssignmentNotes AN 
          WHERE AN.AssignmentID = A.AssignmentID)
      WHEN NULL THEN 0 ELSE 1 END
    AS BIT) AS HasNotes
FROM Assignments A

Answer 3

DECLARE @Assignments TABLE
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(30) NOT NULL
)

DECLARE @AssignmentNotes TABLE
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    AssignmentId INT NOT NULL,
    Note VARCHAR(MAX)
)

INSERT INTO @Assignments(Name) VALUES('Biology')
INSERT INTO @Assignments(Name) VALUES('Chemistry')

INSERT INTO @AssignmentNotes (AssignmentId, Note) VALUES(1, 'Studies on DNA')
INSERT INTO @AssignmentNotes (AssignmentId, Note) VALUES(1, 'Evolution notes from Darwin')

SELECT
    A.*,
    CASE WHEN COUNT(AN.Id) > 0 THEN 1 ELSE 0 END AS HasNotes    
FROM    
    @Assignments AS A
    LEFT JOIN
    @AssignmentNotes AS AN
    ON A.Id = AN.AssignmentId
GROUP BY
    A.Id,
    A.Name

Answer 4

SELECT a.*, 
    (SELECT COUNT(*) 
     FROM assignment_notes an 
     WHERE an.assignmentid = a.id) as NumNotes
FROM Assignment a

That will give you the number of notes with that assignment.

Answer 5

This approach means that you don't need a huge GROUP BY statement as a result of returning lots of Assignment fields.

SELECT Assignment.*, 
CAST(CASE WHEN NotesQty>0 THEN 1 ELSE 0 END as bit) AS HasNotes
FROM Assignment
LEFT JOIN
(SELECT AssignmentId,COUNT(*) AS NotesQty 
 FROM assignment_notes
 GROUP BY AssignmentId) as Assignment_NotesQty
ON Assignment_NotesQty.AssignmentId=Assignment.AssignmentId