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

Question

I have two columns as a company and a product.

I use the following query to get products matching a specific 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 the products of a particular company, how can I do this?

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 connection is enough. Try the following:

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%'

, , , ( "" ) where. having.

Answer 2

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'