Getting rows of a table based on query on another table

Question

I have these 2 tables

[Products]
ID
CompanyID,
Name,
PartNo,
IDSGroup,
ChartNo

[Company]
ID,
Name,
RegistrationNo,
RegistrationDate

I want to show these columns from tables when user search for a company name:

Company.Name, Product.Name, Product.PartNo, Product.IDSGroup

This search query let me get the CompanyID

Select Company.ID from Company WHERE Company.Name LIKE "$userSearch%"

Now I want to use Company.ID for this query to get all product

Select * from Products WHERE CompanyID = "id from previous query"

I don't know how/where to store Company.ID from first query to use it for second query? — Hadi Sharghi, May 06 '14 at 09:18

score 2 · Accepted Answer · answered May 06 '14 at 09:18

2

If it's in the same database you can just join the two tables

SELECT
Company.Name, Products.Name, Products.PartNo, Products.IDSGroup
FROM
Company
LEFT JOIN Products
ON Company.ID = Products.CompanyID
WHERE
Company.Name LIKE "$userSearch%"

answered May 06 '14 at 09:18

Drixson Oseña

3,631
3
23
36

Getting rows of a table based on query on another table

1 Answers1