Determine if "item" in parent table or child table

Databases
21

WOW! This is interesting. How would INSERT INTO be executed?

22

I don’t understand the question.
You would insert into those tables exactly as above.

23

Yes, but the Query for doing that, I am confused.
Is there a possibility for you to show in my sqlfiddle link!

24

How did you generate the INSERT queries for the fiddle? Do the same thing, but with the changed structure.

1 Like
25

I will TRY! I’ll LET YOU KNOW!

26 
CREATE TABLE `items`(
	`item_id` BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
	`name` VARCHAR(255) NOT NULL, 
	`date_added` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

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

CREATE TABLE `item_category`(
	`item_id` BIGINT(10) UNSIGNED NOT NULL, 
	`category_id` BIGINT(10) UNSIGNED NOT NULL,
	FOREIGN KEY (`item_id`) REFERENCES `items`(`item_id`),
	FOREIGN KEY (`category_id`) REFERENCES `categories`(`category_id`)
);

INSERT INTO `items`(`name`) VALUES('Pepsi');
INSERT INTO `items`(`name`) VALUES('Pepsi MAX');

INSERT INTO `categories`(`name`) VALUES('Pepsi');
INSERT INTO `categories`(`name`, `parent`) VALUES(`Pepsi Zero`, 1);

INSERT INTO `item_category` (`item_id`, `category_id`) VALUES
(1, 1);

INSERT INTO `item_category` (`item_id`, `category_id`) VALUES
(2, 2);

Does NOT work in the sqlfiddle.

27

why have you put backticks around the words Pepsi Zero?

Backticks are for column/table/database references. Quote marks (single or double) are for strings.

28

OOPS!

29 
SELECT
i.*,
c.name
FROM items i
INNER JOIN item_category ic ON i.item_id = ic.item_id
INNER JOIN categories c ON ic.category_id = c.category_i

How do I determine if item is coming from Parent or Child inside parent?

30

Select c.parent as well. If c.parent is Null, then the category is a base category. If it’s not, then it’s a subcategory.

1 Like
31

So, CASE IF c.parent == NULL, right?

32

I wouldnt use the database engine to make the evaluation; If you only want base categories, add a “WHERE c.parent == NULL” to your query. If you only want subcategories, add the not-equals to version. If you’re going to use both in your page, pull all categories and then use your scripting language to divvy up the results.

1 Like
33

Actually,

https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html

3.3.4.6 Working with NULL Values

The NULL value can be surprising until you get used to it. Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.

To test for NULL , use the IS NULL and IS NOT NULL operators, as shown here:

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+
2 Likes
34

Absolutely correct. My brain jumped ahead into scripting language and even declared it with a double-equals :laughing:

1 Like
35

Can I use -1 instead?

36

you could, but it will seriously mess you up

1 Like
37

Why?

38

it is possible to do this, but i have yet to meet the person who faithfully implements it

it’s 6th normal form, or something

you end up with hunnerts of little tables (1 or 2 columns only)

39

try it – the best teacher is experience

“don’t touch the hot stove”
“why?”
“it will seriously mess you up”
“why?”
“go ahead, try it”
“OUCHIES!!”
“here, have a tissue and some aloe”

40

So what would be a preferable default value?