CODEWITHSUNDEEP
mysql
Claremont
Claremont

Reputation: 345

Mysql where from two tables

I have two mysql tables called Product and Category

Product

id        name           category        
1         Product #1     1           
2         Product #2     1               
3         Product #3     1               
4         Product #4     2

Category

id        name                  
1         Category #1              
2         Category #2                 
3         Category #3                 
4         Category #4

How would I call all products where Category #1 is true? Essential use the actually name instead of the number so that mysql would see that category.name = category.id and then call all the products based on that.

Upvotes: 0

Views: 104

Answers (2)

keithhatfield
keithhatfield

Reputation: 3273

Simple Join:

SELECT * FROM products AS p 
 LEFT JOIN categories AS c ON p.category = c.id 
 WHERE c.name LIKE 'Category #1'

Upvotes: 1

DRapp
DRapp

Reputation: 48139

Select 
      P.*
   from
      Category C
         JOIN Products P
            on C.ID = P.Category
   where
      C.Name = 'Category #1'

This way, it immediately STARTS with your category table by its name, then goes to products that match the by the proper internal ID link reference.

Upvotes: 0

Related Questions