Data Retrieval issue. - 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 2005 Forums

Transact-SQL (2005)

Data Retrieval issue.

Author

Topic

manavsi
Starting Member

3 Posts

Posted - 2010-05-27 : 05:17:00

Hi,

I have a table which includes the following columns.

ID1,ID2,Mng,Sup.

The data in column "Mng" and "Sup" must be either ID1 or ID2. I have used a self join based on ID1 column. so data that has ID1 in "Mng" and "Sup" is only getting reflected. What do i need to do if i also need the data that has ID2 in "Mng" and "Sup" columns. I have used "or" in join condition. But this is giving me duplicate rows and I think this is not good practice. How could I get all the data? Please guide me.

Thanks in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-27 : 05:35:15

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 05:52:11

Sample data would be fine.
But a guess:
take your first select based on ID1
make a second select based on ID2
then use UNION to get one resultset

i.e:
select ... (here is your first select)
UNION ALL
select ... (here is your second select)

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

manavsi
Starting Member

3 Posts

Posted - 2010-05-27 : 06:19:58

Hi,

Following is the sample data...

ID1 | Name | ID2 | Mng Sup
10001 | XXX | 101
10002 | YYY | 102 | 10001
10003 | ZZZ | 103 | 10001
10004 | WWW | 104 | 10001
10005 | TTT | 105 | 104
10006 | UUU | 106 | 104

Following is the query I am using

select e1.ID2,e1.Name,e2.ID2,e3.ID2 @emp e1
inner join @emp e2 on e1.Mng=e2.ID1
inner join @emp e3 on e1.Sup=e3.ID1

But I get only

10002
10003
10004

I need 10005,10006 also. Please guide me.

Sachin.Nand

2937 Posts

Posted - 2010-05-27 : 10:17:50

[code]
declare @tbl as table(ID1 int, Name varchar(10), ID2 varchar(10), [Mng Sup] varchar(10))

insert into @tbl
select'10001','XXX','101','0'union all
select '10002','YYY','102','10001' union all
select '10003','ZZZ','103','10001' union all
select '10004','WWW','104','10001' union all
select '10005','TTT','105','104' union all
select '10006','UUU','106','104'union all
select '10007','UUU','106','102'

select * from @tbl

select T1.ID1 from
(
select ID1,ID2 from @tbl t1 where ID1=some(select [Mng Sup] from @tbl t2)
or ID2=some(select [Mng Sup] from @tbl t2)
)T inner join @tbl T1 on T.ID1= T1.[Mng Sup] or T.ID2= T1.[Mng Sup]

[/code]

Hopefully someone else comes up with a much better solution.

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-27 : 10:21:39

The sample data provided by the OP is wrong!
Mng and Sup should be 2 columns!
I will not go on here with wrong data

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

Sachin.Nand

2937 Posts

Posted - 2010-05-27 : 10:39:13

quote:
Originally posted by webfred

The sample data provided by the OP is wrong!
Mng and Sup should be 2 columns!
I will not go on here with wrong data

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

S***T !!!!!
What the F**K ?

PBUH

manavsi
Starting Member

3 Posts

Posted - 2010-05-28 : 02:56:27

quote:
Originally posted by webfred

The sample data provided by the OP is wrong!
Mng and Sup should be 2 columns!
I will not go on here with wrong data

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

Yes. Mng and Sup are two columns.

ID1 | Name | ID2 | Mng | Sup
10001 | XXX | 101
10002 | YYY | 102 | 10001 | 10002
10003 | ZZZ | 103 | 10001 | 10002
10004 | WWW | 104 | 10001 | 10002
10005 | TTT | 105 | 104 | 10002
10006 | UUU | 106 | 104 | 10002

Hope data is correct now. Could you please help me now..!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-28 : 02:59:33

put code-tags around for readable formatting:
[C0DE]



ID1 | Name | ID2 | Mng    | Sup
10001 | XXX | 101
10002 | YYY | 102 | 10001 | 10002 
10003 | ZZZ | 103 | 10001 | 10002 
10004 | WWW | 104 | 10001 | 10002 
10005 | TTT | 105 | 104   | 10002 
10006 | UUU | 106 | 104   | 10002

[/C0DE]

And now, for better understanding of your requirements, it would be very helpful if you could post the needed output in the same way.

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

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-05-28 : 05:02:48

[code]
DECLARE @test AS TABLE ( ID1 int , Name varchar(10), ID2 int, Mng int, Sup int)

INSERT INTO @test
SELECT 10001 , 'XXX' , 101, NULL, NULL
UNION ALL SELECT 10002 , 'YYY' , 102 , 10001 , 10002
UNION ALL SELECT 10003 , 'ZZZ' , 103 , 10001 , 10002
UNION ALL SELECT 10004 , 'WWW' , 104 , 10001 , 10002
UNION ALL SELECT 10005 , 'TTT' , 105 , 104 , 10002
UNION ALL SELECT 10006 , 'UUU' , 106 , 104 , 10002

SELECT * FROM @Test a
WHERE mng in ( Select id1 from @test ) OR sup IN ( SELECT id1 FROM @test )
OR mng in ( Select id2 from @test ) OR sup IN ( SELECT id2 FROM @test )
[/code]

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources