Requiring Query for my expected RESULT *****************************

Posted by Thiru under Sql Server on 3/26/2012 | Points: 10 | Views : 2567 | Status : [Member] | Replies : 13

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi Friends,

It will be great if you help me to get a query for my expected result as follows:

Use the sql create table:

CREATE TABLE `tblbusiman` (
  `WMC` varchar(15) NOT NULL DEFAULT '',
  `Wnm` varchar(250) NOT NULL DEFAULT '',
  `Intro` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`WMC`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

Insert data are:

INSERT INTO `tblbusiman` (`WMC`,`Wnm`,`Intro`) VALUES ('A1','RAJU','CMP');
INSERT INTO `tblbusiman` (`WMC`,`Wnm`,`Intro`) VALUES ('A1A','RAJU WIFE 1','A1');
INSERT INTO `tblbusiman` (`WMC`,`Wnm`,`Intro`) VALUES ('A1AA','R-WIFE 1 SON','A1A');
INSERT INTO `tblbusiman` (`WMC`,`Wnm`,`Intro`) VALUES ('A1B','RAJU WIFE 2','A1');
INSERT INTO `tblbusiman` (`WMC`,`Wnm`,`Intro`) VALUES ('A2','BABU','CMP');
INSERT INTO `tblbusiman` (`WMC`,`Wnm`,`Intro`) VALUES ('A2A','BABU WIFE 1','A2');
INSERT INTO `tblbusiman` (`WMC`,`Wnm`,`Intro`) VALUES ('CMP','Company','C');

My expected/required Result (WMC):

IF user select WMC = A1 THEN


A1A
A1AA
A1B

IF user select WMC=CMP THEN


A1
A1A
A1AA
A1B
A2
A2A

Note: i am using mysql db. but no matter about query.

Expecting your valuable reply
Regards,
Thiru.

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Jmckamal on: 3/26/2012 [Member] Starter | Points: 25

0	Here is the query, also attaching screenshot of the results. select WMC from tblbusiman where WMC like '%A1%' and intro not in ('CMP') select WMC from tblbusiman where WMC not in ('CMP') Download source file Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Thiru on: 3/27/2012 [Member] Starter | Points: 25

0	Hi Kamal, You made a nice try. But i hope you didn't look on the table logic. WMC means BusinessMan code Intro means (its also businessman code where it refers the person who introduced) So, as per the logic we can just use %A1% to get the result. (ofcourse as per the example you made it - but i just mention this as an sample data but in real the data will be A1,A1A,abcd,efc,xxx,yyyy like so) as in code we can do as: x1= select wmc,intro from tblbusiman where intro=A1 do until x1.eof console.write(x1(wmc) x2=select wmc,intro from tblbusiman where intro=x1("wmc") do until x2.eof console.write(x2(wmc) x2.movenext loop x1.movenext loop Note: i just mention the logic. so, dont look on syntax regards, Thiru. Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Hi Kamal,
You made a nice try. But i hope you didn't look on the table logic.

WMC means BusinessMan code
Intro means (its also businessman code where it refers the person who introduced)

So, as per the logic we can just use %A1% to get the result.
(ofcourse as per the example you made it - but i just mention this as an sample data but in real
the data will be A1,A1A,abcd,efc,xxx,yyyy like so)

as in code we can do as:

x1= select wmc,intro from tblbusiman where intro=A1

do until x1.eof

    console.write(x1(wmc)



   x2=select wmc,intro from tblbusiman where intro=x1("wmc")

   do until x2.eof

      console.write(x2(wmc)

    x2.movenext

   loop



x1.movenext

loop

Note: i just mention the logic. so, dont look on syntax
regards,
Thiru.

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajni.Shekhar on: 3/27/2012 [Member] Bronze | Points: 25

0	same logic you can apply in sql procedure.... Thanks, Rajni Shekhar Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Thiru on: 3/27/2012 [Member] Starter | Points: 25

0	Hi Rajni Shekhar, can you give me a sample code with same logic - please. (if possible use mysql stored procedure - please) regards, Thiru. Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jmckamal on: 3/27/2012 [Member] Starter | Points: 25

0	If you give me the real data with clear explanation I can able to provide you just by writing a query/stored Proc. Please mention the word for example during giving such kind of tables. Please send me your real table. so that i can do analysis to provide the query. Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Thiru on: 3/27/2012 [Member] Starter | Points: 25

0	hi Kamal, Thanks for your reply. There is nothing more than sample data i have mentioned above. (more over - there is a very huge real data and it requires me to map all the things to send it. - its not possible right now) hope you understand. WMC is business man code INTRO is the person who introduced this WMC (this is also business man code) TARGET IS: Finding how many person WMC is introduced and example: cmd1= how many person WMC introduced (select wmc from tblbusiman where intro='A1' cmd2= how many person cmd1.WMC introducde (select wmc from tblbusiman where intro='cmd1.wmc' cmd3= how many person cmd2.WMC introducde (select wmc from tblbusiman where intro='cmd2.wmc' cmd4= how many person cmd3.WMC introducde (select wmc from tblbusiman where intro='cmd3.wmc' it goes till no records found.... More over the structure and DB is same as i mentioned here with in sample data. please use the same and create a stored procedure please. regards, Thiru. Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

hi Kamal,

Thanks for your reply.
There is nothing more than sample data i have mentioned above.
(more over - there is a very huge real data and it requires me to map all the things to send it. - its not possible right now)
hope you understand.

WMC is business man code

INTRO is the person who introduced this WMC (this is also business man code)



TARGET IS: 

Finding how many person WMC is introduced and 

example: 

cmd1= how many person WMC introduced (select wmc from tblbusiman where intro='A1'

cmd2= how many person cmd1.WMC introducde (select wmc from tblbusiman where intro='cmd1.wmc'

cmd3= how many person cmd2.WMC introducde (select wmc from tblbusiman where intro='cmd2.wmc'

cmd4= how many person cmd3.WMC introducde (select wmc from tblbusiman where intro='cmd3.wmc'

it goes till no records found....

More over the structure and DB is same as i mentioned here with in sample data.

please use the same and create a stored procedure please.

regards,
Thiru.

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajni.Shekhar on: 3/27/2012 [Member] Bronze | Points: 25

0	i dont know mySql syntax, following procedure written in sql server 2008. you can change your syntax here crate procedure get_wmc( @wmc varchar(15)) as begin decalre @var_wmc varchar(15); DECLARE @TBL_OP TABLE(WMC VARCHAR(15), INTRO VARCHAR(20)); declare cur_wmc cursor for select wmc, intro from tblbusiman where intro=@wmc OPEN vendor_cursor FETCH NEXT FROM cur_wmc INTO @var_wmc WHILE @@FETCH_STATUS = 0 BEGIN SET @TBL_OP = select wmc,intro from tblbusiman where intro=@var_wmc END SELECT * FROM @TBL_OP end it is not tested but i might work. Thanks, Rajni Shekhar Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

i dont know mySql syntax, following procedure written in sql server 2008. you can change your syntax here

crate procedure get_wmc(
@wmc varchar(15))
as
begin
decalre @var_wmc varchar(15);
DECLARE @TBL_OP TABLE(WMC VARCHAR(15), INTRO VARCHAR(20));

declare cur_wmc cursor for select wmc, intro from tblbusiman where intro=@wmc

OPEN vendor_cursor

FETCH NEXT FROM cur_wmc
INTO @var_wmc

WHILE @@FETCH_STATUS = 0
BEGIN
SET @TBL_OP = select wmc,intro from tblbusiman where intro=@var_wmc

END

SELECT * FROM @TBL_OP

end

it is not tested but i might work.

Thanks,
Rajni Shekhar

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Thiru on: 3/27/2012 [Member] Starter | Points: 25

0	Yes. A1 is introduced by CMP and A1A is introduced by A1 and A1AA is introduced by A1A .. the chain will go upto the max of 12 loops but we have to jump out when there is no "introduced by" Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sabarimahesh on: 3/28/2012 [Member] Bronze | Points: 25

0	Nothing You just Ditto it in Store Procedure Life is a Race Thanks & Regards By Sabari Mahesh P M Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sabarimahesh on: 4/3/2012 [Member] Bronze | Points: 25

0	CGN007 nICE mAN Life is a Race Thanks & Regards By Sabari Mahesh P M Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Thiru on: 4/3/2012 [Member] Starter | Points: 25

0	Dear CGN007, Is it possible to give it in mysql ? or explain the lines before and after loops brackets Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sabarimahesh on: 5/31/2012 [Member] Bronze | Points: 25

0	CGN007 U r Welcome Life is a Race Thanks & Regards By Sabari Mahesh P M Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Thiru on: 6/1/2012 [Member] Starter | Points: 25

0	Hi Friends, Thanks for spending your valuable time here. Actually i solved it in a differ method - but that's not the correct solution. Hope i will get the expected one from your post here - Thanks for it. I will work on it and get back to you in this. and will mark the perfect answer too. Once again thanks for your valuable time here. Thiru, if this helps please login to Mark As Answer. \| Alert Moderator

Hi Friends,
Thanks for spending your valuable time here.
Actually i solved it in a differ method - but that's not the correct solution.
Hope i will get the expected one from your post here - Thanks for it.

I will work on it and get back to you in this.
and will mark the perfect answer too.

Once again thanks for your valuable time here.

Thiru, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts