SELECT with distinction

[cpu278]

Hi, I looked for any posts on this but couldn't find any, it should be pretty easy.

I have a Company table with parent_id that points to a company itself. I want to get the names of the parent, if any. So if I have my table as

cpy_id cpy_name parent_id
1111 "ABBB" 0
2222 "CDDD" 0
3333 "ABBB Japan" 1111
4444 "CDDD China" 2222

and I have a set of values (1,4) I should get the names for 1 and 2, not 1 and 4.

Thanks in advance

[tesuji]

Hi

I am really confused, how is your "set of values (1,4)" related to your example table? An why you decide that then you should get the names 1 and 2? The names 1 and 2 point to what of your example table?

I understand that your table is kind of recursive table which defines a tree because parent_id points to rows in same table, however I don´t see the pair (1,4) in that table.

-- tesu

[cpu278]

Hi

I am really confused, how is your "set of values (1,4)" related to your example table? An why you decide that then you should get the names 1 and 2? The names 1 and 2 point to what of your example table?

I understand that your table is kind of recursive table which defines a tree because parent_id points to rows in same table, however I don´t see the pair (1,4) in that table.

-- tesu

Hi tesuji, thanks for replying.

My bad, I wasn't that clear. What I meant is that I can have certain company IDs from a previous query (say, 1 and 4) and I want to find out the parents for these companies. For 1, the parent is itself, but for 4, the parent is 2, so the query I'm asking for should return data for companies 1 and 2.

[tesuji]

In your example say, 1 and 4, is 1 identical to 1111 respectively 4444? if so, this select could help:

select cpy_id, cpy_name from cpytable where cpy_id in (select parent_id from cpytable where 'your criteria to get 1 and 4');

However, if 1 and 4 kind of level, than I would add level number to your table:

cpy_id cpy_name       parent_id   level
1111   "ABBB"         0             1
2222   "CDDD"         0             1
3333   "ABBB Japan"   1111          2
4444 "CDDD China"     2222          2

-- and the select could be for example:
select cpy_id, cpy_name from cpytable where cpy_id in (select parent_id from cpytable where level = 2);
/* result
1111   "ABBB"
2222   "CDDD" 
*/

Btw, there is a famous guy on the internet: Joe Celko, he wrote a book only dealing with SQL and trees, hierarchies. You may google celko trees sql.

-- tesu

[cpu278]

Oops again! It's the first case, which worked but only for 4444 which returned 2222. For 1111, since the parent_id is 0 and not 1111, it is not included. It should really be pointing to itself (1111), but I'm not sure if I can change this. Any additional tweak to include the 0 values in parent_id?

Thanks in advance!

[tesuji]

You are using "0" for labelling the root of a tree. If "0" is a numeric value, it may function. If it were NULL, you can't get the root by my first select. For example:

SELECT cpy_id, cpy_name FROM cpytable WHERE cpy_id IN (SELECT parent_id FROM cpytable WHERE cpy_id=4444 OR parent_id=0);

gives parent of 4444 and ALL parents having parent_id = 0, those are all parents theirselves. To differentiate between the root parents why not setting parent_id = cpy_id, as for example:

cpy_id cpy_name   parent_id 
1111 "ABBB"       1111
2222 "CDDD"       2222
3333 "ABBB Japan" 1111
4444 "CDDD China" 2222

-- and the first select could work:

SELECT cpy_id, cpy_name FROM cpytable WHERE cpy_id IN (SELECT parent_id FROM cpytable WHERE cpy_id = 4444 OR cpy_id = 2222);

-- tesu

[cpu278]

Oops, didn't close this one. Your first code worked tesuji, I changed the 0 values to the company_id itself and it did the job. Thank you!