CODEWITHSUNDEEP
mysql
Chetan Patel
Chetan Patel

Reputation: 183

SQL not working properly

i have this query but this shows column does not exist

SELECT 
p.content,
(select count(*) from share_member WHERE username='pratik' AND share_id=share) as allow 
FROM posts p WHERE allow >="1"

and i got this error

Unknown column 'allow' in 'where clause'

EDIT: This edit has been done to improve sql query for my use

SELECT 
p.content,
CASE share_type 
WHEN 'public' then "1"
when 'lsit' then 
    (select count(*) from share_member WHERE username='pratik' AND share_id=share)
end as allow
FROM posts p WHERE allow=>1

Error code 1064, SQL state 42000

Upvotes: 0

Views: 75

Answers (3)

poncha
poncha

Reputation: 7866

You cannot use column aliases in WHERE clause.

You can, however, use HAVING clause instead, there you can use aliases. Difference being that WHERE is performed while constructing resultset, and HAVING is applied after the whole set is matched (which means in general, that HAVING is heavier).

SELECT 
p.content,
(select count(*) from share_member WHERE username='pratik' AND share_id=share) as allow 
FROM posts p
HAVING allow >= 1

EDIT: Your second query should look like this:

SELECT p.content,
  CASE
    WHEN share_type='public' THEN "1"
    WHEN share_type='list' THEN (SELECT COUNT(*) FROM share_member WHERE username='pratik' AND share_id=share)
    END AS `allow`
FROM posts p
HAVING `allow` >= 1

Upvotes: 2

Prashant Mehta
Prashant Mehta

Reputation: 482

Your query modified as

SELECT 
p.content,
(select count(*) from share_member WHERE username='pratik' AND share_id='share') as allow 
FROM posts p WHERE allow >=1

Or

SELECT 
p.content,
(select count(*) from share_member WHERE username='pratik' AND share_id='share') as allow 
FROM posts p WHERE allow >='1'

Upvotes: 0

Rahul Agrawal
Rahul Agrawal

Reputation: 8971

It should be

SELECT p.content,
    (select count(*) 
     from share_member 
     WHERE username='pratik' AND share_id=share) as allow 
FROM posts p 
WHERE (select count(*) 
      from share_member 
      WHERE username='pratik' AND share_id=share) >=1

or 

(select count(*) 
 from share_member 
 WHERE username='pratik' AND share_id=share)

should be from clause, as another result set

Upvotes: 1

Related Questions