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

Posted by Thiru under Sql Server on 3/26/2012 | Points: 10 | Views : 1319 | Status : [Member] | Replies : 24
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.




Responses

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

Up
0
Down
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

Up
0
Down

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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: CGN007 on: 3/27/2012 [Member] Silver | Points: 25

Up
0
Down
Is 'A1AA' is also there in the result when WMC=CMP?

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

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

Up
0
Down
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: CGN007 on: 3/27/2012 [Member] Silver | Points: 25

Up
0
Down
Otherwise you can try this
   SELECT wmc FROM tblbusiman WHERE Intro IN(SELECT wmc FROM tblbusiman WHERE intro='A1')OR Intro='A1'


SELECT wmc FROM tblbusiman WHERE Intro IN(SELECT wmc FROM tblbusiman WHERE intro='CMP')OR Intro='CMP'



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

Posted by: CGN007 on: 3/27/2012 [Member] Silver | Points: 25

Up
0
Down
If so please ignore the previous code..
Let me try another one...

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

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

Up
0
Down
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: CGN007 on: 3/29/2012 [Member] Silver | Points: 25

Up
0
Down
Here it is

    WITH Emp_CTE AS 

(
SELECT wmc, Wnm, Intro
FROM dbo.tblbusiman
WHERE Intro='CMP'
UNION ALL
SELECT e.WMC, e.Wnm, e.Intro
FROM dbo.tblbusiman e
INNER JOIN Emp_CTE ecte
ON ecte.WMC = e.Intro
)
SELECT WMC FROM Emp_CTE
GO


Hope this will give the desired ouput...!!!

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

Posted by: CGN007 on: 3/29/2012 [Member] Silver | Points: 25

Up
0
Down
" IF user select WMC = A1 THEN "
A1A
A1AA
A1B
Please change the where part to get the result for this


WITH Emp_CTE AS 

(
SELECT wmc, Wnm, Intro
FROM dbo.tblbusiman
WHERE Intro='A1'
UNION ALL
SELECT e.WMC, e.Wnm, e.Intro
FROM dbo.tblbusiman e
INNER JOIN Emp_CTE ecte
ON ecte.WMC = e.Intro
)
SELECT WMC FROM Emp_CTE
GO


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

Posted by: CGN007 on: 4/2/2012 [Member] Silver | Points: 25

Up
0
Down
Mark as Answer if its helpful to you,that motivates...

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

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

Up
0
Down
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

Up
0
Down
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: CGN007 on: 5/30/2012 [Member] Silver | Points: 25

Up
0
Down
Here we are using a common table expression(CTE).
Please refer this http://msdn.microsoft.com/en-us/library/ms190766%28v=sql.105%29.aspx



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

Posted by: CGN007 on: 5/30/2012 [Member] Silver | Points: 25

Up
0
Down
@Sabarimahesh
Thanks man...!!!

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

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

Up
0
Down
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: CGN007 on: 6/1/2012 [Member] Silver | Points: 25

Up
0
Down
@Thiru
Hope you understand it.
Mark as Answer if its helpful to you,that motivates...

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

Posted by: CGN007 on: 6/1/2012 [Member] Silver | Points: 25

Up
0
Down
@Sabarimahesh
:-)

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

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

Up
0
Down
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

Posted by: CGN007 on: 6/1/2012 [Member] Silver | Points: 25

Up
0
Down
@Thiru
U r Welcome...:-)

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

Login to post response