plz help me, for a single query

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

plz help me, for a single query

Author

Topic

vedjha
Posting Yak Master

228 Posts

Posted - 2009-02-03 : 03:16:43

i have to fetch Upline_ID.

select upline_id as aid ,advisor_id as isid ,1 as level from members
where advisor_id=50010001 and placement='L'

suppose result:

aid isid
2 1

now query is:

select upline_id as aid ,advisor_id as isid ,1 as level from members
where advisor_id=2

then Result:

aid isid
4 2
5 2

now query is:

select upline_id as aid ,advisor_id as isid ,1 as level from members
where advisor_id=4

and

select upline_id as aid ,advisor_id as isid ,1 as level from members
where advisor_id=5

is there any way to fetch data by a single query
Database is: My SQL

Ved Prakash Jha

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-03 : 03:23:24

select upline_id as aid ,advisor_id as isid ,1 as level from members
where advisor_id=4
union
select upline_id as aid ,advisor_id as isid ,1 as level from members
where advisor_id=5

or select upline_id as aid ,advisor_id as isid ,1 as level from members
where advisor_id in (4,5)

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-03 : 03:25:07

with cte(aid,isid)
as
(
select upline_id as aid ,advisor_id as isid ,1 as level from members
where advisor_id=50010001 and placement='L'
union all
select upline_id as aid ,advisor_id as isid ,1 as level from members m
inner join cte on (m.upline_id=cte.isid)
)
select * from cte

Jai Krishna

vedjha
Posting Yak Master

228 Posts

Posted - 2009-02-03 : 03:33:15

advisor_id=4 /5
How to assign it
first query is:

select upline_id as aid ,advisor_id as isid ,1 as level from members
where advisor_id=50010001 and placement='L'

i know only first value as advisor_id=50010001
output:

aid isid
2 1

now query is:

select upline_id as aid ,advisor_id as isid ,1 as level from members
where advisor_id= 2 //(How to know there is 2 may be result will 3 or any thing)

after that query will return 2 records
and then i have to run query for all records
...

Ved Prakash Jha

vedjha
Posting Yak Master

228 Posts

Posted - 2009-02-03 : 03:35:17

with CTE (Common Table Expressions) does not support in My sql Databases
it supports in SQL 2005-08

Ved Prakash Jha

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-02-03 : 04:05:53

quote:
Originally posted by vedjha

with CTE (Common Table Expressions) does not support in My sql Databases
it supports in SQL 2005-08

Ved Prakash Jha

then maybe you should post on a MySQL forum instead of a SQL Server one?

Em

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-02-03 : 06:21:20

Try this:

select 
m3.upline_id as aid ,
m3.advisor_id as isid ,
1 as level 
from members m1
join members m2 on m2.advisor_id = m1.advisor_id
join members m3 on m3.advisor_id = m2.advisor_id
where m1. advisor_id = 50010001 and m1.placement = 'L'

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

vedjha
Posting Yak Master

228 Posts

Posted - 2009-02-03 : 06:47:06

Thnks :
I have runed the query

3=50010003
4=50010004
output:

aid isid level

3 1 1
3 1 1
4 1 1
4 1 1

but i expect output as:

aid isid level

3 1 1
4 1 1
5 3 2
6 3 2
7 4 2
8 4 2
9 5 3
10 6 3
11 10 4
12 11 5

Ved Prakash Jha

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-02-03 : 07:17:52

Then please give us sample data...

No, you're never too old to Yak'n'Roll if you're too young to die.

vedjha
Posting Yak Master

228 Posts

Posted - 2009-02-03 : 07:38:14

aid isid level

3 1 1
4 1 1
5 3 2
6 3 2
7 4 2
8 4 2
9 5 3
10 6 3
11 10 4
12 11 5

it is sample

Ved Prakash Jha

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-03 : 08:48:28