Determine if "item" in parent table or child table

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;
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

http://sqlfiddle.com/#!9/7ec5b8/47

fixed

Where? In the fiddle?

in my reply

Unknown column ‘i.subcategory_id’ in ‘on clause’

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

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!

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

http://sqlfiddle.com/#!9/7ec5b8/35

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?

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!

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

in my humble opinion, no

Categories and Subcategories

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.

AAAaaauuugh!!!

you meant to say NULL, not 0

and BIGINT is ~way~ too big

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

https://sqllessons.com/categories.html

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

Interesting:

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