I have 2 DB tables which I want to combine in 1 query.
stores :
Expand|Select|Wrap|Line Numbers
- +-id-+-Stores name-+
- | | |
- | 1 | first |
- | 2 | second |
- | 3 | third |
- +----+-------------+
Expand|Select|Wrap|Line Numbers
- +-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:
Expand|Select|Wrap|Line Numbers
- SELECT storesid FROM products WHERE productname='pen'
Expand|Select|Wrap|Line Numbers
- SELECT * FROM stores WHERE id=3 OR id=2
So the question is if I can do this job in one query.
Thanks / Behzad