select statement in where clause sql

Question

What is the problem with my query I can't figure it out.

Select id, name, description, road_st, sub_area, area, website, email, category,
           working_hrs, inside_building, logo, latitude, longitude, 
           geom.STNumPoints() as vertices, 
           geom.STAsText() as geom,house_no,building,shop
            from Points 
            where @hr.STIntersects(geog)= 1  
            and deleted=0
            and (select s.name from Sub_Category s where s.id=category) 
            like  '%'+@text+'%'

ERROR :
if @text='store'

I am getting no result... but there is a row in SUb_category table named "Departmental Store"

the query is working fine till

Select id, name, description, road_st, sub_area, area, website, email, category,
           working_hrs, inside_building, logo, latitude, longitude, 
           geom.STNumPoints() as vertices, 
           geom.STAsText() as geom,house_no,building,shop
            from Points 
            where @hr.STIntersects(geog)= 1  
            and deleted=0

it gives no results when i add this line

            and (select s.name from Sub_Category s where s.id=category) 
            like  '%'+@text+'%'

whats wrong with this line?

if @test=" store" I am getting no result... but there is a row with in SUb_category table named "Departmental Store" — user3382149, Mar 05 '14 at 06:17
may be because you have a typo??? , your query says `@text` and error you posted says `@test`.. :) — NoobEditor, Mar 05 '14 at 06:19
@user3382149 try changing "store" to store alone with single quotes — Rat-a-tat-a-tat Ratatouille, Mar 05 '14 at 06:20
what is the query you are getting when you do this? Are you passing in the parameter? — ps2goat, Mar 05 '14 at 06:24
Select id, name, description, road_st, sub_area, area, website, email, category, working_hrs, inside_building, logo, latitude, longitude, geom.STNumPoints() as vertices, geom.STAsText() as geom,house_no,building,shop from Points where @hr.STIntersects(geog)= '1' and deleted='0' and (select s.name from Sub_Category s where s.id=category) like '%'+@text+'%' — , Mar 05 '14 at 06:25

score 1 · Answer 1 · answered Mar 05 '14 at 06:23

1

Select id, name, description, road_st, sub_area, area, website, email, category,
           working_hrs, inside_building, logo, latitude, longitude, 
           geom.STNumPoints() as vertices, 
           geom.STAsText() as geom,house_no,building,shop
            from Points 
            where @hr.STIntersects(geog)= '1'  
            and deleted='0'
            and (select s.name from Sub_Category s where s.id=category) 
            like  '%'+@text+'%'

try this

answered Mar 05 '14 at 06:23

1

adding an explanation as to what has been done by u in the query, which was not there before is a good sign.... :) – NoobEditor Mar 05 '14 at 06:25
@user3291757 it dint work.... delete is INT type column and @hr.STIntersects(geog) als0 returns INT – user3382149 Mar 05 '14 at 06:35

score 0 · Accepted Answer · answered Mar 13 '14 at 05:27

SELECT 
            p.id, 
            p.name, 
            s.name as street, 
            sa.name as sub_area, 
            a.name as area, 
            c.name as city,
            p.website,
            p.email, 
            pr.name as province,
            p.description, 
            sc.name as category, 
            u.UserName as AddedBy, 
            u1.UserName as EditedBy, 
            p.date_added, p.date_edited, 
            p.latitude, 
            p.longitude, 
            p.working_hrs, 
            p.inside_building,
            p.shop as shop,
            p.geom.STNumPoints() as vertices, p.geom.STAsText() as geom,
            hn.name as house,(select abc.name from Points abc where abc.id=p.building)as building 
            from Points p inner join Road_Street s on p.road_st = s.id 
            inner join Subarea sa on p.sub_area = sa.id 
            inner join Area a on p.area = a.id
            inner join City c on a.city = c.id 
            inner join Sub_Category sc on p.category = sc.id 
            inner join aspnet_users u on p.added_by = u.UserId 
            inner join aspnet_users u1 on p.edited_by = u1.UserId 
            inner join Province pr on c.province = pr.id
            inner join House_Number hn on p.house_no = hn.id
            where @hr.STIntersects(p.geog)= 1 
            and p.deleted=0
            and (select stt.name from Sub_Category stt where stt.id=p.category) like  '%'+@text+'%'

Description/Correction: Nothing was wring with the query, the error occurred due to an ambigious column name.... in the second last line i added "p." with delete. it worked fine!

select statement in where clause sql

2 Answers2