Finding all children for multiple parents in single SQL query and Linq query

Question

I need to SELECT query using LINQ and also SQL resulting as:

ParentId   Country   State         ChildId
 1         India     kerala           2
 1         India     TamilNamdu       3
 5         Pakistan  Kasagithan1      6
 5         Pakistan  Kasg2            7

And my Table is:

Id  ParentId Country        State
1   0        India          NULL
2   1        NULL           Kerala
3   1        NULL           TamilNamdu
4   1        NULL           Karnadaka
5   0        Pakisthan      NULL
6   5        NULL           Kasagithan
7   5        NULL           Kasg2
8   5        NULL           Afganistha
9   0        China          NULL
10  9        NULL           Hwuesang1
11  9        NULL           sate1
12  9        NULL           sate2

I suggest you change your design. You can assume every `Country`, `State` is a single entity (Table) — Saro Taşciyan, Feb 07 '14 at 11:44
Well yes but what's the advantage besides the trouble it brings? — Saro Taşciyan, Feb 07 '14 at 12:06

Yuvi · Answer 1 · 2014-02-07T12:34:39.697

1

Try this SQL query:

select parentid, country, state, childID 
from tablename 
where parentid IN (1,5)

edited Feb 07 '14 at 12:34

answered Feb 07 '14 at 11:46

Yuvi

185
2
10

1

It will retrieve all the rows that has ID 5 and 1 . – Yuvi Feb 07 '14 at 11:58
-1: `it will retrieve all the rows that has ID 5 and 1` which should be `ID 1 to 5 (between 1 to 5)` which in case isn't the correct answer for the question. The question is about relating states with their countries not filtering IDs – Saro Taşciyan Feb 07 '14 at 12:14
@Yuvi you are welcome but altho it's corrected now, it still doesn't give the answer for the question. The result will be *states of India and Pakisthan* with `Country` field `NULL` – Saro Taşciyan Feb 07 '14 at 12:41

score 1 · Answer 2 · answered Feb 07 '14 at 12:07

1

In SQL a self join should do it:

SELECT P.Id AS ParentId, P.Country, C.State, C.Id AS ChildId
FROM table AS P
JOIN table as C ON C.ParentId = P.Id AND C.ParentId <> 0
WHERE P.State IS NULL

answered Feb 07 '14 at 12:07

Fredrik Ljung

1,445
13
28

@Federik, thanks i have tested it i got result in sql – Sajith Feb 07 '14 at 12:16

Saro Taşciyan · Answer 3 · 2014-02-07T12:18:13.953

You can self-join the table using Id and ParentId columns. The following code is LINQ implementation of this approach:

using (YourEntity yourEntity = new YourEntity())
{
    var result =
    (
        from state in yourEntity.YourTableName
        from country in yourEntity.YourTableName
        where state.ParentId != 0 && state.ParentId == country.Id
        select new { ParentId = state.ParentId, Country = country.Country, State = state.State, ChildId = state.Id }
    ).ToList();
}

You can test the result using Console: (or change it with Debug.WriteLine() to see results in output window if you can't use console)

foreach (var item in result)
{
    Console.WriteLine("{0} {1} {2} {3}", item.ParentId, item.Country, item.State, item.ChildId);
}

And for SQL Query you can use:

SELECT state.ParentId, country.Country, state.State, state.Id As 'ChildId'
FROM YourTableName As state INNER JOIN YourTableName AS country
    ON state.ParentId <> 0 AND state.ParentId = country.Id

@Sajith Hope you will (: – Saro Taşciyan Feb 11 '14 at 07:57 — Saro Taşciyan, Feb 11 '14 at 07:57

Finding all children for multiple parents in single SQL query and Linq query

3 Answers3