Filtering based on subquery result

I have mysql query and I want to filter based on gigcount when i use where clause ie where gigcount=0 I get error unknown column

SELECT
tbl_musicvenue.id AS id,

(SELECT

    COUNT(1)AS A1
    FROM
        smliveclaims AS Extent5
    WHERE
        Extent5.tbl_MusicVenueId = tbl_musicvenue.id and (Extent5.IsDeleted =0 or Extent5.IsDeleted is null)) AS gigcount

FROM

tbl_musicvenue

Is there any way to get all items where gigcount is 0

Answer

You can rewrite your query as

SELECT
a.id,
COUNT(b.tbl_MusicVenueId) gigcount
FROM
tbl_musicvenue a
LEFT JOIN smliveclaims b ON a.id = b.tbl_MusicVenueId AND (b.IsDeleted =0 OR b.IsDeleted IS NULL)
GROUP BY a.id
HAVING gigcount = 0

for your question to use gigcount in where clause, Where can be used for known column(s) of table(s) involved in query. In your case gigcount is an alias not a predefined column so you cannot access this in where clause. to filter on such columns use having clause