COUNT Total items from main and subcategory

query practically writes itself

[arnold] you lack discipline [/arnold]

Please share your article.
Also, is the SQL query provided by @m_hutley, the correct way of doing this?
Thanks a lot.

it’s in this thread here – Determine if "item" in parent table or child table - #14 by m_hutley

1 Like

I am curios what is the reason of using many-to-many relations?

1 Like

Also, is the SQL query provided by @m_hutley, the correct way of doing this?

Is there a easier way of doing that?

Single Level Adjacency Rollup-and-Total:

(SELECT i.item_id,
IFNULL(c.parent,c.category_id) AS cat
FROM items i
LEFT JOIN item_category ic ON i.item_id = ic.item_id
LEFT JOIN categories c ON ic.category_id = c.category_id
) iq
LEFT JOIN categories co ON co.category_id =

I guess I don’t know the truth answer to this. Would, category_id inside items as a Foreign Key be a better choice, please let me know. Thanks a lot.

better than what?

Than my current approach of using a “linking” table, item_category ?

The answer to that question is the same as the answer to this one: Does a item only belong to a single category?

1 Like

Yes, it does I guess. Especially if it is a specific item.

Then you’ve answered your own question.

1 Like

Maybe I have. But is this good practice from a DB point!

good practice consists of a robust design that captures all business rules and allows for simple retrieval of data

(the “simple retrieval of data” criterion is why i hate EAV schemes)

how often do you actually have non-specific items? how often does a single item belong to more than one category or subcategory?

1 Like

Thanks a lot. I think I know understand better.

I do not follow everything, but my understanding is this using one-many relations. Need more data for testing.


1 Like

WHILE, this does not work. Sorry man! I tested it.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.