Combining 2 MySQL tables in 1 query

[bnashenas1984]

Hi everyone
I have 2 DB tables which I want to combine in 1 query.


stores :

+-id-+-Stores name-+
|    |             |
| 1  |  first      |
| 2  |  second     |
| 3  |  third      |
+----+-------------+

Products :

+-id+-storesid-+-productname-+
|   |          |             |
| 1 |   3      |  pen        |
| 2 |   2      |  DVD        |
| 3 |   3      |  Hard drive |
| 4 |   1      |  DVD        |
| 5 |   2      |  mp3 player |
| 6 |   2      |  pen        |
| 7 |   1      |  Hard drive |
+---+----------+-------------+

I'm sure this is possible but I don't know how

as you can see the first table contains the name and ID of each store. and the second one contains products of each store.

Here's what I need to do :

When a user searches for a product (Ex. pen) the query should return all of the stores that have that product.

Right now i'm doing this by 2 queries. First I get the ID of those stores like this:

SELECT storesid FROM products WHERE productname='pen'

Then I fetch all of the IDs and put them in an array and then I execute the second query like this :

SELECT * FROM stores WHERE id=3 OR id=2

As you know this query could get very long depending on the number of stores which have that product

So the question is if I can do this job in one query.



Thanks / Behzad

[yarbrough40]

SELECT * 
FROM stores JOIN products on stores.id = products.storesid
WHERE products.productname = 'pen'

Enjoy!

[bnashenas1984]

Thanks yarbrough40
I appreciate your help