Try to Solve This - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Try to Solve This

Author

Topic

jawsai1224
Starting Member

3 Posts

Posted - 2009-07-07 : 18:07:50

I have 2 tables in my database

table I : db_parentchild
--------------------------
id |parent_id |child_id|
1 | 12 | 14 |
2 | 14 | 29 |
3 | 14 | 17 |
4 | 17 | 20 |
5 | 17 | 18 |
6 | 21 | 23 |
7 | 24 | 25 |
8 | 25 | 27 |
--------------------------

table II : db_main
--------------------------
id |name|parent_child_id|value|
1|fox|14|0.14|
2|fox|15|0.13|
3|fox|12|0.01|
12|fox|29|0.23|
13|fox|17|0.04|
4|gim|14|0.02|
5|gim|17|0.03|
6|gim|20|0.05|
7|gim|18|0.05|
8|ros|24|0.04|
9|ros|25|0.03|
10|deu|12|0.01|
11|deu|13|0.01|
------------------------------

conditions
step1. First find minimum value for each name (I solved this by the following statement)

SELECT id,name,parent_child_id,value
FROM db_main
WHERE value=(SELECT min(value) FROM db_main as db_temp where db_temp.name=db_main.name);

result
---------------------
id|name|parent_child|value|
1|fox|12|0.01|
4|gim|14|0.02|
9|ros|25|0.03|
10|deu|12|0.01|
11|deu|13|0.01|
---------------------

step2. check if parent_child of each name has any child_id from table I : db_parentchild

In our case fox, 12 has child 14, 14 exists in parent_child_id under the same name fox. same for gim 14 has child 17 which exists in parent_child_id from table II : db_main under the same name gim.

for ros, 25 has child 27 but 27 does not exist in parent_child db_main table under name ros and the same for 12 and 13 child's are 14 and 15 respectively from table I but they donot exists in table II under the name deu.

so result table is changed to

generation 1.
---------------------
id|name|parent_child|value|
1|fox|14|0.14|
5|gim|17|0.03|
9|ros|25|0.03|
10|deu|12|0.01|
11|deu|13|0.01|
---------------------

step 3: step 2 is repeated second time then the result is generation 2. there are 2 child exist for gim 17 they are 20,18 which exists in table 2 db_main both has same values so we must choose both.

generation 2.

-------------------------
id|name|parent_child|value|
1|fox|14|0.14|
6|gim|20|0.05|
7|gim|18|0.05|
9|ros|25|0.03|
10|deu|12|0.01|
11|deu|13|0.01|
--------------------------
step 4: step 2 is repeated to get next generation. here 14 has child 29 and 17 both exist in the table 2 db_main, so we must choose the one which had least value in table 2 under the name fox. so in this case 17 is choose because it has the lowest value 0.04 compares to 29 which has 0.23

generation 3

id|name|parent_child|value|
-------------------
13|fox|17|0.04|
6|gim|20|0.05|
7|gim|18|0.05|
9|ros|25|0.03|
10|deu|12|0.01|
11|deu|13|0.01|
--------------------
Further generations are not possible because there is no child for parent child in table 1 or child is present but it does not exists for the given name in the table 2 db_main.

Final result should be :

id|name|parent_child|value|
-------------------
13|fox|17|0.04|
6|gim|20|0.05|
7|gim|18|0.05|
9|ros|25|0.03|
10|deu|12|0.01|
11|deu|13|0.01|
--------------------

Could please help me design a sql statement to check these conditions?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-07 : 18:13:33

What comes into my mind is to use a recursive cte.

N 56°04'39.26"
E 12°55'05.63"

jawsai1224
Starting Member

3 Posts

Posted - 2009-07-07 : 18:20:10

Thanks a lot for giving me the tips, I am new to sql till now I have used only basic select statements with some function statements. It would be great if you give more info on "recursive cte" thing.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-07 : 21:33:11

http://msdn.microsoft.com/en-us/library/ms186243.aspx

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources