CODEWITHSUNDEEP
phpmysqlsqljoin
user1740512
user1740512

Reputation: 15

Parent Child Relationships PHP and MYSQL

I have a table like this:

**id     name     parent_id**
1       X         2 
2       Y         2
3       Z         1
4       A         5
5       B         6
6       C         1


I want output look like this: 
**name       *parent name***
X           Y
Y           Y
Z           X
A           B
B           C
C           X

Is it possible to do it with one query by using JOIN?

Upvotes: 1

Views: 1214

Answers (4)

h4cky
h4cky

Reputation: 894

You can use JOIN to do this

Example:

SELECT
    table.name
    , parent.name as parent_name
FROM
    table
LEFT JOIN
    parent ON parent.parent_id = table.parent_id
WHERE
    table.visible = 1

After that you will handle the response from your query in php cycle through returned array of items(array's or object's - depends on what MySQL abstraction you use), save the needed in your own array, or just encoding this array to JSON.

Note: I don't know for what you need this, but it's good idea to include table.id in the result, if you want latter to be able to identify one record.

Upvotes: 0

Avinash Dubey
Avinash Dubey

Reputation: 128

Let's say you have 2 tables:

  1. product
  2. product_parent

Then, you run a query like this:

SELECT product.name, product_parent.name
FROM product
INNER JOIN product_parent
ON product.product_parent_id=product_parent.id

Upvotes: 0

eggyal
eggyal

Reputation: 125865

You need to perform a self-join:

SELECT child.name AS `name`, parent.name AS `parent name`
FROM   my_table AS child JOIN my_table AS parent ON parent.id = child.parent_id

See it on sqlfiddle.

Upvotes: 4

fd8s0
fd8s0

Reputation: 1927

select son.name as name, father.name as parent_name from table_name as son left join table_name as father ON son.parent_id = father.id

Upvotes: 1

Related Questions