select items whose category is X and whose category's parent is X

Question

I have two tables, Items and Categories.

Items has recno, sku, description, price, brand, category, details, gender, size, color, dateadded. Categories has recno, category, parent.

The query I need, must select items whose category is X and whose category's parent is X.

I've tried

SELECT DISTINCT items.recno, 
                items.sku, 
                items.description, 
                items.price, 
                items.brand, 
                items.category, 
                items.details, 
                items.gender, 
                items.size, 
                items.color, 
                items.dateadded 
FROM   `items` 
       INNER JOIN `categories` 
               ON items.category = categories.parent 
ORDER  BY `description` 

but that just selects everything. I tried to use joins but was never able to grab the items from the child categories.

Answer 1

Please try this:

SELECT DISTINCT 
    items.recno, 
    items.sku, 
    items.description, 
    items.price, 
    items.brand,
    items.category, 
    items.details, 
    items.gender, 
    items.size, 
    items.color, 
    items.dateadded 
FROM `items` 
JOIN `categories` ON items.category = categories.parent 
WHERE categories.category='x' AND categories.parent='X'

you have not added the WHERE condition in the query, that's why the result shows all rows