COUNT Total items from main and subcategory

Databases
21

query practically writes itself

[arnold] you lack discipline [/arnold]

22

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

23

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

1 Like
24

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

1 Like
25

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 co.name, COUNT(DISTINCT iq.item_id) FROM
(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 = iq.cat
GROUP BY co.name
26

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.

27

better than what?

28

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

29

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
30

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

31

Then you’ve answered your own question.

1 Like
32

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

33

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
34

Thanks a lot. I think I know understand better.

35

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

OR http://sqlfiddle.com/#!9/38cf10/2

1 Like
36

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

37

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