问题:

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 "Table2" 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

回答1:

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

回答2:

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

回答3:

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