CODEWITHSUNDEEP
phpmysql
user1595097
user1595097

Reputation: 1

mysql one to many query in php

I am trying to retrieve the parent record then retrieve the matching child records for that record in PHP from MySQL DB.

ParentTable

ISN|ParentName|JoinedDate
1  |John      |01-01-2010
2  |Sam       |02-02-2010
3  |Sheila    |13-08-2012

ChildTable

ISN|ChildName|JoinDate
1 |Mary      |10-10-2010
1 |Tamsin    |11-10-2010
3 |Kyle      |11-12-2010

The query result should look like this:

1|John  |Parent|01-01-2010
1|Mary  |Child |10-10-2010
1|Tamsin|Child |11-10-2010
2|Sam   |Parent|02-02-2010
3|Sheila|Parent|13-08-2012
3|Kyle  |Child |11-12-2010

I tried using outer join and join, using a temp table to combine and query the result I don't seem to find one way that works out right. How can I achieve this?

Upvotes: 0

Views: 103

Answers (3)

celsowm
celsowm

Reputation: 404

Using "UNION"

select ISN, ParentName, 'Parent' as 'Filiation', JoinedDate from ParentTable

UNION

select ISN, ChildName, 'Child' as 'Filiation', JoinDate from ChildTable

Upvotes: 0

DarkFighter
DarkFighter

Reputation: 253

Use the following query:

SELECT ISN, ParentName, 'Parent', JoinedDate
UNION SELECT ISN, ChildName, 'Child', JoinDate`

But it's better to save data about parents and childs in single table with additional field type (for exmaple: 0 - parent, 1 - child).

Upvotes: 0

John Woo
John Woo

Reputation: 263693

Just use UNION in order to combine the rows from both table. The column name must be the same that's why I used alias in the child's table.

SELECT ISN, ParentName AS PersonName, 'Parent' AS `Status`, JoinedDate
FROM parentTable
UNION
SELECT ISN, ChildName AS PersonName, 'Child' AS `Status`, JoinDate AS JoinedDate
FROM childTable
ORDER BY ISN, `Status` DESC

Upvotes: 1

Related Questions