CODEWITHSUNDEEP
phpmysql
user1099407
user1099407

Reputation:

How to get value from parent_id in mysql?

I have a sample data

game(id, name)
      1 | Avatar
      2 | Angry Bids
brand(id, parent_id, name)
      1 | 0 | Apple
      2 | 0 | Samsung
      3 | 1 | Iphone 4
      4 | 2 | Galaxy Tab
game_brand(game_id, brand_id)
              1   | 4
              2   | 3

And query:

SELECT game.name, 
       game_brand.brand_id, 
       (SELECT brand.parent_id 
        FROM   brand AS brand 
        WHERE  brand.brand_id = game_brand.brand_id) AS brand_father_id 
FROM   game AS game 
       LEFT JOIN game_brand AS game_brand 
              ON game_brand.game_id = game.id 
WHERE  game.id = 2

AND result:

name       | brand_id |    brand_father_id
Angry Bids |    3     |       1

How to get the values from brand_id, brand_father_id, eg:

game       | brand    | brand parent
Angry Bids | Iphone 4 | Apple

Upvotes: 3

Views: 444

Answers (3)

Anthony
Anthony

Reputation: 37055

Obviously if the contraints of your model and query are taken as purely abstract, Nikola's answer is spot on. But for anyone that finds there way to this question because they have a similar model as well as a similar goal, here is a different approach:

Remove the unneeded restraint of the brand hierarchy by splitting the brand table into two separate tables:

brand(id, manufacturer_id, name)
      1 | 1 | Iphone 4
      2 | 2 | Galaxy Tab

manufacturer(id, name)
      1 | Apple
      2 | Samsung

This is based on certain assumptions I'm extrapolating from the sample data model:

  1. A game will never be directly related to a "parent brand"
  2. A "child brand" will never be a "parent brand"

If the above are true, the manufacturer table is more appropriate/normalized and reduces the query to:

SELECT 
     game.name AS game, 
     brand.name AS brand, 
     manufacturer.name AS manufacturer
FROM game 
     JOIN game_brand ON (game.id = game_id)
     JOIN brand ON (brand_id = brand.id)
     JOIN manufacturer ON (brand.manufacturer_id = manufacturer.id)

The above will return all games that have relationships to brands which had relationships to manufacturers. To get all games even if they only have a brand, you would want to use a left join for at least the last join.

Upvotes: 0

M_A_K
M_A_K

Reputation: 378

try this one

SELECT
  game.name,    
  brand.name          AS brand_father_name,
  brndName.name       AS brand_name
FROM game AS game
  LEFT JOIN game_brand AS game_brand
    ON game_brand.game_id = game.id
  LEFT JOIN brand AS brand
    ON brand.id = game_brand.brand_id
  LEFT JOIN brand AS brndName
    ON brndName.id = brand.parent_id
WHERE game.id = 2

Upvotes: 0

Nikola Bogdanović
Nikola Bogdanović

Reputation: 3213

SELECT game.name, child.name AS brand_name, parent.name AS brand_father_name
FROM game_brand INNER JOIN game ON game_brand.game_id = game.id
INNER JOIN brand AS child ON game_brand.brand_id = child.id
INNER JOIN brand AS parent ON child.parent_id = parent.id
WHERE game.id = 2

UPDATED

SELECT game.name, child.name AS brand_name, (SELECT name FROM brand AS parent WHERE child.parent_id = parent.id) AS brand_father_name
FROM game_brand INNER JOIN game ON game_brand.game_id = game.id
INNER JOIN brand AS child ON game_brand.brand_id = child.id
WHERE game.id = 2

UPDATED AGAIN

AND YET AGAIN just now noticed that the OP wanted not just the fathers name, but also the child one's (and not their ids also), so updated them both...

Upvotes: 1

Related Questions