Problem in sql query

Question

2.33/5 (3 votes)

See more:

SQL-Server-2008

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 8-Jan-12 21:56pm

Randeep Chauhan

Updated 8-Jan-12 22:00pm

Mehdi Gholam

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Amir Mahfoozi · Accepted Answer · 2012-01-08T22:35:00

Solution 2

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.

Posted 8-Jan-12 22:35pm

Amir Mahfoozi

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.

Amir Mahfoozi 10-Jan-12 1:54am

You're welcome Parveen.