Oscommerce Question posted in
The official documentation can be found here

Fetch parent categories that have no sub_category or children in osCommerce

In my osCommerce store I want to fetch the parent_id of categories that either have no sub category or child categories.

I used the following in my code:

$categories_query = tep_db_query("select c.categories_id, cd.categories_name 
    from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd 
    where c.parent_id = '$parent_id' and 
    c.categories_id = cd.categories_id 
    order by sort_order, cd.categories_name");

The above translates into this when the constants are filled in:

select c.categories_id, cd.categories_name 
    from categories c, categories_description cd 
    where c.parent_id = '$parent_id' and 
    c.categories_id = cd.categories_id 
    order by sort_order, cd.categories_name
Tags:

2

Answers


  1. 0

    Without knowing your table layouts, there’s some guessing needed to answer your question. But, I believe you need a query like this:

         SELECT c.parent_id
       FROM TABLE_CATEGORIES c
  LEFT JOIN TABLE_CATEGORIES_DESCRIPTION cd ON c.categories_id = cd.categories_id
      WHERE cd.categories_id IS NULL

    Note that this LEFT JOIN ... WHERE ... IS NULL pattern matches the rows in the first table joined that don’t have a match in the second table.

    Login or Signup to reply.
  2. 0

    You can go with this:

    SELECT c.categories_id, cd.categories_name, c.parent_id 
  FROM categories c, categories_description cd 
  WHERE c.categories_id=cd.categories_id 
  AND c.categories_id NOT IN (SELECT DISTINCT c2.parent_id FROM categories c2)
  ORDER BY c.sort_order, cd.categories_name

    or you can go with that:

    SELECT c.categories_id, cd.categories_name, c.parent_id 
  FROM categories c 
  LEFT JOIN categories_description cd 
  ON c.categories_id=cd.categories_id
  WHERE c.categories_id NOT IN (SELECT DISTINCT c2.parent_id FROM categories c2)
  ORDER BY c.sort_order, cd.categories_name

    But what you really want to know about is this line:

      c.categories_id NOT IN (SELECT DISTINCT c2.parent_id FROM categories c2)

    Since the parent_id column is in the same table as the one you’re looking up and not in another you’ll need to look up the table on itself and reference it via another alias.

    This part of the SQL will return all the categories_id (actually parents_id in the table) that are being referenced as parent categories: 

    SELECT DISTINCT c2.parent_id FROM categories c2

    Now, when you put that into a NOT IN comparison you’re left with rows that do not have any children categories underneath.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search