0

How do I select all data from one table, based on all data selected from a different table?

For example:

Table 1: categories

+-----+------------+
| cid |  category  |
+-----+------------+
| 1-9 | red boots  |
| 1-9 | blue boots |
| 1-5 | pants      |
| 1-9 | pink boots |
+-----+------------+

Table 2: (raw data)

+------------+-----------+----------+
|  category  | materials | shipping |
+------------+-----------+----------+
| red boots  | cotton    |        8 |
| red boots  | snow      |        9 |
| pants      | leather   |        8 |
| blue boots | leather   |        5 |
| pink boots | glitter   |        4 |
+------------+-----------+----------+

My (bad) pseudo-code:

SELECT category 
FROM `categories` 
WHERE cid = '1-9'

then 

select * 
from 'raw data' 
where category = all my categories.category

If I search for '1-9', I'm hoping for this result:

+------------+---------+---+
| red boots  | cotton  | 8 |
| red boots  | snow    | 9 |
| blue boots | leather | 5 |
| pink boots | glitter | 4 |
+------------+---------+---+

closed as off-topic by Paul White Aug 30 '15 at 8:23

If this question can be reworded to fit the rules in the help center, please edit the question.

1

This is a simple SQL:

select t2.category, t2.materials, t2.shipping
from   categories t1, raw_data t2
where  t1.cid     ='1-9'
and    t1.category=t2.category;

or

select t2.category, t2.materials, t2.shipping
from   categories t1 join raw_data t2 on t1.category=t2.category
where  t1.cid     ='1-9';

Not the answer you're looking for? Browse other questions tagged or ask your own question.