Determine if "item" in parent table or child table

Databases
1

Is there a way to know or compare two tables, here are of course 5 tables.
Two act as linking Tables, while the other ones hold data.

SQL-query:

SELECT 
c.name, isub.name
FROM subcategories isub
LEFT JOIN item_subcategory ic ON isub.subcategory_id = ic.subcategory_id
LEFT JOIN items i ON ic.item_id = i.item_id
LEFT JOIN categories c ON isub.category_id = c.category_id
LEFT JOIN item_category itmc ON c.category_id = itmc.category_id
WHERE i.name = 'Pepsi MAX';

My fiddle: http://sqlfiddle.com/#!9/7ec5b8/24

Diagram:
enter image description here

EDIT: Tested:

SELECT 
c.name, isub.name
FROM subcategories isub
LEFT JOIN item_subcategory ic ON isub.subcategory_id = ic.subcategory_id
LEFT JOIN items i ON ic.item_id = i.item_id and i.name = 'Pepsi'
LEFT JOIN categories c ON isub.category_id = c.category_id
LEFT JOIN item_category itmc ON c.category_id = itmc.category_id;
2 
SELECT 'subcat' AS where_found
     , isub.name
  FROM items AS i
INNER
  JOIN item_subcategory AS ic 
    ON ic.subcategory_id = i.subcategory_id   
INNER
  JOIN subcategories AS isub
    ON isub.subcategory_id = ic.subcategory_id
 WHERE i.name = 'Pepsi MAX'    
UNION ALL
SELECT 'cat'
     , c.name
  FROM items AS i
INNER
  JOIN item_category AS itmc 
    ON itmc.category_id = i.category_id
INNER
  JOIN categories AS c 
    ON c.category_id = isub.category_id
 WHERE i.name = 'Pepsi MAX'
1 Like
3
4

fixed

5

Where? In the fiddle?

6

in my reply

7

Unknown column ā€˜i.subcategory_idā€™ in ā€˜on clauseā€™

8

oh crap

i have to go outā€¦

do you think you could fix it? i mean, it should be patently obvious where the typo occurred

9

Itā€™s too heavy! Can this be done in a better more efficient way, https://stackoverflow.com/questions/58426509/determine-if-item-in-parent-table-or-child-table - see the thread there, I was told to change the order, but I donā€™t know how to. Thx!

10

A RIGHT JOIN B = B LEFT JOIN A.

A RIGHT JOIN B RIGHT JOIN C = C LEFT JOIN B LEFT JOIN A

Yes this is a complete sentence. Stupid forum software.

1 Like
11
12

Iā€™m not sure I follow the sense of the join tables in your flow.
Since, by definition, a Subcategory has a Category, Why store item_category and item_subcategory separately?

13

I guess, I was stupid. I thought about having linking tables and then joining them. So if an item is in subcategory it would be joined with item_subcategory linking table.

Is this good approach?
I guess, this means that item can be in multiple categories, hmm! Bad design choice, I guess.

What would you suggest? Thanks a lot for pointing out!

14

Well this is where I make a suggestion and then get my wrist slapped by Rudy shortly thereafter :stuck_out_tongue:
If it were me, iā€™d combine the subcategory and category tables thusly:

CREATE TABLE `categories`(
	`category_id` BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	`name` VARCHAR(255) NOT NULL
        `parent` BIGINT(10) UNSIGNED DEFAULT 0 
);

If itā€™s a Category, parent is 0, if itā€™s a subcategory, parent is the ID of its parent category. Since youā€™ve specified a 1-level hierarchy, this would satisfy all data storage.

1 Like
15

in my humble opinion, no

Categories and Subcategories

16

You mean subcategory holds parent_id? Because thatā€™s what I have in my sqlfiddle. But then I want to place an item inside a specific category or subcategory, so I used linking tables.

17

AAAaaauuugh!!!

you meant to say NULL, not 0

and BIGINT is ~way~ too big

18

See, iā€™ve always had it railed to me that you should avoid NULL columns at all costs, and the BIGINT was because iā€™m matching the datatype of the ID column of the same table. (I didnt define that column. It probably doesnt need to be a BIGINT.)

1 Like
19

https://sqllessons.com/categories.html

So, self linking is the approach?
What about items?

Interesting:

20

Youā€™d only have 1 join table - Say your Pepsi MAX belongs to the ā€œDrinksā€ category, and the ā€œSodasā€ subcategory.

Item table:
ID 1: Name ā€œPepsi Maxā€ (etc.)

Categories table:
ID 1: Name ā€œDrinksā€: Parent 0 (or NULL)
ID 2: Name ā€œSodaā€: Parent 1

Item_categories table:
ItemID: 1 CategoryID: 1
ItemID: 1 CategoryID: 2

1 Like