Need SQL Query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Need SQL Query

Author

Topic

asyssolvers
Starting Member

9 Posts

Posted - 2011-02-23 : 15:00:52

Please see below tables;

Table A
DROPID______CHILDID______Type
drpanu005____0000359453____1
drpanu005____0000359454____1
drpanu006____0000359455____1
drpanu007____0000359456____1
DRPANU010____ DRPANU005____4
DRPANU010____DRPANU006____4

Table B
CaseID______DropID____Qty
0000359453__ drpanu005__ 3
0000359454__ drpanu005__ 7
0000359455__ drpanu006__ 10
0000359456__ drpanu007__ 10

In above scenario I need to have those data as you can see below,
You have to link table B to table A and take data as follow,
Please show me the query required.

(DropID ) AS MasterBox____ SubBox____ Qty
DRPANU010____ drpanu005__ 10
DRPANU010____ drpanu006__ 10
NULL__________ drpanu007__ 10

this is like we have box "drpanu005", "drpanu006", "drpanu007" and out of the first two box goes in to another box "drpanu010", so now "drpanu005", "drpanu006" become child of box "drpanu010". and "drpanu007" still independent without parent box. So I need to shoe that senario using above table structure and sample data.

Thankz in advance!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-23 : 21:47:25

will you have more than 2 level of parent - child in Table A ?

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

asyssolvers
Starting Member

9 Posts

Posted - 2011-02-24 : 05:22:30

Yes of cause.,. U got the idea. :D

quote:
Originally posted by khtan

will you have more than 2 level of parent - child in Table A ?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-24 : 08:12:08

can you post such sample data and show us the expected result ?

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

asyssolvers
Starting Member

9 Posts

Posted - 2011-02-27 : 01:20:30

Thank you very much for your cooperation,

Here it is,.

(DropID ) AS MasterBox____ SubBox____ Qty
DRPANU010____ drpanu005__ 10
DRPANU010____ drpanu006__ 10
NULL__________ drpanu007__ 10

quote:
Originally posted by khtan

can you post such sample data and show us the expected result ?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-27 : 03:52:38

quote:
Originally posted by asyssolvers

Thank you very much for your cooperation,

Here it is,.

(DropID ) AS MasterBox____ SubBox____ Qty
DRPANU010____ drpanu005__ 10
DRPANU010____ drpanu006__ 10
NULL__________ drpanu007__ 10

quote:
Originally posted by khtan

can you post such sample data and show us the expected result ?

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

what is this ? sample data or expected result ?

post some sample data that have multi level case and show us what is the result that you want.

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

asyssolvers
Starting Member

9 Posts

Posted - 2011-02-27 : 23:59:06

Table B
ChildID ______ DropID ______ Qty
0000359453 ______ drpanu005 ______ 3
0000359454 ______ drpanu005 ______ 7
0000359455 ______ drpanu006 ______ 10
0000359456 ______ drpanu07 ______ 12
0000359457 ______ drpanu08 ______ 10

Table A
DropID ______ ChildID ______ Type
drpanu005 ______ 0000359453 ______ 1
drpanu005 ______ 0000359454 ______ 1
drpanu006 ______ 0000359455 ______ 1
drpanu07 ______ 0000359456 ______ 1
drpanu08 ______ 0000359457 ______ 1
drpanu010 ______ drpanu005 ______ 4
drpanu010 ______ drpanu006 ______ 4
drpanu011 ______ drpanu07 ______ 4
drpanu011 ______ drpanu010 ______ 4

Output/Result
Master Box ID ______ Child Box ID ______ Qty

drpanu011 ______ drpanu010 ______ 20
drpanu011 ______ drpanu07 ______ 12
NULL ______ drpanu08 ______ 10

Note:
*here we cant see "drpanu05" and "drpanu06", that is OK, because they are child of "drpanu010". And then "drpanu010" become child of "drpanu011" .
* And "drpanu08" is independent and it has no parent , so it become with NULL the Parent Box ID.
* In table A , "type" value "4" means they are become child/parent relation. and "type" value "1" means normal.

Hope your undestanding and waiting for a reply.

Thanks and Best Regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources