How to use where condition for the for a selected column using subquery?

Question

I have two columns as company and product.

I use the following query to get the products matching particular string...

select id,(select name from company where product.cid=company.id) as 
company,name,selling_price,mrp from product where name like '$qry_string%'

But when i need to list products of specific company how can i do?

i tried the following but in vein

select id,(select name from company where product.cid=company.id) as
company,name,selling_price,mrp from product where company like '$qry_string%'

Help me

Answer 1

What you are trying to do does not require a subquery, a simple join is enough. Try this:

select c.name, p.id, p.name, p.selling_price, p.mrp
  from company c
 inner join product p
    on c.id = p.cid
 where c.name like '$qry_string%'

I think the problem with the query you tried is that you cannot use fields that are the result of a subquery (in your case, "company") in the where clause. You might try having instead.

Answer 2

You can use

SELECT p.id, c.name AS company, p.name, p.selling_price, p.mrp FROM product p, company c WHERE p.cid=c.id AND c.name LIKE '$qry_string'