Determine if "item" in parent table or child table

Databases
41

just to follow up on this idea, of using 0 as the FK to avoid a NULL, try this simple experiment –

CREATE 
  TABLE categories
      ( category_id SMALLINT     NOT NULL PRIMARY KEY
      , name        VARCHAR(255) NOT NULL
      , parent      SMALLINT     NOT NULL
      , CONSTRAINT valid_parent
          FOREIGN KEY ( parent )
            REFERENCES categories ( category_id )
      );

you will see that the structure is perfectly reasonable, the parent column is declared NOT NULL so that you can use a 0

now go ahead and insert a row, i’ll wait

42

NULL !!!

43

I’ll see.

44

I am trying to change column name, based if parent or child, BUT I keep failing.

CASE WHEN c.parent = NULL THEN 
CASE WHEN c.parent != NULL THEN
45

use IS or IS NOT – there is no equals

46

Yes, I did that too.

CASE WHEN c.parent IS NULL THEN c.name END AS Child,
CASE WHEN c.parent IS NOT NULL THEN c.name END AS Parent

The issue is that I am getting both of them.

47

i think you need to go back to the manual and check the actual syntax for a CASE expression – you’re missing something

1 Like
48

DO I need IF or something?
E.g. ternary operator ?

49

let me assure you that the answer is in the manual

i will not look it up for you, you need to learn how to do that yourself

1 Like
50

Which manual?

51

Sure. But there’s NOTHING about setting different column alias. Sorry.

52

https://dev.mysql.com/doc/refman/5.7/en/case.html

53

That’s what I’ve been doing:

CASE WHEN 
c.parent IS NULL THEN c.name
END AS Child, 
CASE WHEN 
c.parent IS NOT NULL THEN c.name 
END AS Parent

BUT HOW DO I GET ONLY ONE OF THEM IN THE OUTPUT?

54 
SELECT c.name
     , CASE WHEN c.parent IS NULL
            THEN 'parent'
            ELSE 'child' END AS what_it_be
  FROM ...
1 Like
55

I see your approach. BUT, is it possible to modify column alias and output only one?

56

nope

1 Like
57

I see, thx.

58

Hi, why isn’t this SQL working as expected?

SELECT i.*, 
 parent.name AS 'Parent', 
 child.name AS 'Child'
 FROM items i
 LEFT JOIN categories child ON i.category_id = child.category_id
 LEFT JOIN categories parent ON child.parent = parent.category_id
 WHERE i.name = "Pepsi";

I am getting Parent = NULL, while Child equals the name of the Parent. Is there something wrong in my order of LEFT JOINs ?

59

So I will assume your table still has Pepsi in the category Pepsi, which has no parent.

LEFT JOIN categories child ON i.category_id = child.category_id
So, “Pepsi”'s category id matches the category ID for “Pepsi”. (Naming your item and your category the same is going to make this confusing.)

LEFT JOIN categories parent ON child.parent = parent.category_id
The Pepsi category’s parent field is NULL. so this doesnt join anything; or rather, it joins no data to the new columns.

parent.name, then, is NULL.
child.name, is Pepsi.

This is expected behavior.

1 Like
62

Ah. Didn’t know this.

Pepsi = Parent main category.
Pepsi Zero = Child category.

Is there a way to overcome?