trace - 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

General SQL Server Forums

New to SQL Server Programming

trace

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-17 : 00:28:21

hi all..
i have 2 tbl..


-- Prepare sample data
DECLARE	@tbl1 TABLE (box varchar(10), loc varchar(5) )

INSERT	@tbl1
SELECT	'P1', 'aa'  UNION ALL
SELECT	'P1', 'bb'  UNION ALL
SELECT	'P1', 'aa'  UNION ALL
SELECT	'P3', 'cc'

DECLARE	@tbl2 TABLE (box varchar(10), loc varchar(5) )

INSERT	@tbl2
SELECT	'P1', 'aa'  UNION ALL
SELECT	'P3', 'cc'

--expected result
SELECT 'P1' as box, 'aa' as Location, 'aa' as HeaderLoc UNION ALL
SELECT 'P1' as box, 'bb' as Location, 'aa' as HeaderLoc

how do i trace from @tbl1, that has 1 Box with more than 1 Location?
in this case P1 has 2 distinct location (aa & bb).. and left join in the loc from @tbl2 just to retrieve the loc for that box..

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-17 : 00:42:40

Try this (Not tested)

Select T1.box, T1.loc as location, T2.loc as HeaderLoc from @tbl1 T1 inner join @tbl2 T2
on T1.box=T2.box where T1.box in (Select box from @tbl1 group by box having count(*)>1)

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-17 : 00:47:11

[code]

select a.box as Box, b.loc as Location, c.loc as HeaderLoc
from
(
select box
from @tbl1
group by box
having count(*) > 1
) a
inner join
(
select box, loc
from @tbl1
group by box, loc
) b on a.box = b.box
inner join @tbl2 c on a.box = c.box
[/code]

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-17 : 01:57:46

thanks tan :) :) :)

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-17 : 02:08:47

tan,..
if i omit the record box='P1', loc='bb', so the remain records will be
1. P1,aa
2. P1,aa
3. P3,aa

i expect to get nothing.. bcoz 1 box (P1) has 1 loc (aa).. what i want to search is IF the P1 has more than 1 type of location... it is ok to has many P1 with same location (aa)... hopefully u get what i mean..

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-17 : 02:18:54

Sorry, misunderstood your earlier requirement.


select	a.box as Box, b.loc as Location, c.loc as HeaderLoc
from
(
	select	box
	from	@tbl1
	group by box
	having count(distinct loc) > 1
) a 
inner join 
(
	select	box, loc
	from	@tbl1 
	group by box, loc
) b 	on a.box = b.box
inner join @tbl2 c on a.box = c.box

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-05-17 : 05:12:51

Oh thank u so muchasss tan...

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources