February 23, 2011 at 12:54 pm
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!!
February 23, 2011 at 1:49 pm
It looks like a simple join using the ChildID will do this.
select TA.DropID, TB.DropID, sum(Qty)
from TableA
inner join TableB
on TableA.ChildID = TableB.CaseID
group by TA.DropID, TB.DropID;
I'm assuming that you want to sum up the quantity column, based on your sample data.
If I'm correct and this is what you need to do, this is very simple, basic SQL. If you'll need to do this kind of thing at all, you should read up on SQL basics. Maybe take a class or do some of the online tutorials.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 23, 2011 at 3:04 pm
thank you very much for your quick reply.
That is not the result tht i want.
In Table A,. DropID become ChildID in somecases where typrid = 4 sometimes..
DRPANU010____ DRPANU005____4
DRPANU010____DRPANU006____4
so it means Qty 10 + 10 = 20 going to the DropID "DRPANU010".
Please see the 3rd output window in my first post.. that is the data I want.
In your query I miss last two rows,...You will get an idea if you read last sentence in my 1st post.
Hope your undestanding.
February 24, 2011 at 7:18 am
You'll need to write down all the rules for joining the tables. Otherwise, I'm left guessing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply