Click here to Skip to main content
15,569,840 members
Please Sign up or sign in to vote.
2.33/5 (3 votes)
hi friends....
its my table structure
tbdep 
id  name
1   Sale
2   A/c

tbemp
id Name did
1  abc   1
2  xyz   2
3  aaa   1

i want following output:
Sale
abc
aaa
A/c
xyz

how to solve this problem..
Thanks
Posted
Updated 8-Jan-12 23:00pm
v2

1 solution

Despite the fact that you don't want to use grouping features of you Report or Grid or anything else that shows your data, here is SQL that produces that output for proving that everything is possible in SQL . But repeatedly I suggest using grouping features in UI or Report.

SQL
with a
as(
select tbdep.id, tbdep.name , cast(cast(tbdep.id as varchar)  + 'd' as varchar)  as dn from tbdep
union all
select t.id, t.name,cast( cast(a.id as varchar) +  cast(t.id as varchar)as varchar)  as dn from tbemp t inner join a on  t.did = a.id where a.dn like '%d'
)
select name from a
order by dn desc


Hope it helps.
 
Share this answer
 
Comments
Randeep Chauhan 9-Jan-12 5:00am    
Thank sir it's working ,i will try grouping
Amir Mahfoozi 9-Jan-12 5:32am    
You're welcome. And if it was working so please mark it as an answer [and/or] vote it up :)
RasikaLB 9-Jan-12 23:10pm    
select t.id, t.name,cast( cast(a.id as varchar) + cast(t.id as varchar)as varchar) as dn from tbemp t inner join a on t.did = a.id where a.dn like '%d'

i think this code can replace by following code without where part using tbdep table

select t.id, t.name,cast( cast(ab.id as varchar) + cast(t.id as varchar)as varchar) as dn from tbemp t inner join tbdep ab on t.did = a.id

btw nice work Amir i hvnt use "with" in sql thanks

bt heard it's only supported in SQL Server (2005+, called Common Table Expressions) and Oracle (9i+, called Subquery Factoring).
Amir Mahfoozi 10-Jan-12 0:27am    
About the replacement statement : I had written firstly something like this and after getting infinite recursion I added a word at the end of anchor table(first statement) IDs to distinguish newly added rows from original rows in "a" table.

Yes it has magical effect on writing queries for example you can use CTEs to avoid calling functions by writing nested CTEs.
For the list of CTE supporting DBMSs see this page :
http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Database_capabilities[^]
And thank you Rasika for the compliment.
Parveen Rathi 10-Jan-12 1:51am    
Thankyou sir, Thankyou very much.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900