Get current record for a subquery in T-SQL

I'm trying to select all records from a table "Table1" but I want a new column called "HasException" that contains a "0" or a "1". "HasException" must be "0" if the count of row matching the current Id from "Tabl开发者_Python百科e2" is equal to 0, else it returns 1.

Here's what I've done so far, but it doesn't works:

SELECT *,
CONVERT(bit, (CASE WHEN (SELECT count(Id) FROM Table2 WHERE Table1.Id=Table2.Id) = 0 THEN 0 ELSE 1 END)) AS HasException
FROM Table1

You want to join the tables (and group on ID) before you can compare the two values like this:

SELECT  dbo.Table_1.*,
    CASE WHEN COUNT(dbo.Table_2.ID) = 0 THEN
        0
    ELSE
        1
    END
    AS HasException
FROM         dbo.Table_1 LEFT OUTER JOIN
                      dbo.Table_2 ON dbo.Table_1.ID = dbo.Table_2.ID
GROUP BY dbo.Table_1.ID

perhaps something like, assuming you meant table2?

SELECT *,
   CAST(CASE WHEN COUNT(table2.id) = 0 THEN 0 ELSE 1 END AS bit) AS HasException
FROM
   Table1
   LEFT JOIN
   Table2 ON Table1.Id=Table2.Id
GROUP BY
   Table1.id

select  
  T1.*,
  case when T2.Id is null then 0 else 1 end as HasException
from Table1 as T1
  left outer join 
    (
      select distinct Id
      from Table2
    ) as T2
    on T1.Id = T2.Id