SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Union parent child records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gkola
Starting Member

4 Posts

Posted - 02/24/2014 :  11:29:48  Show Profile  Reply with Quote
Hi

I have an application that has an existing query which returns org units (parent and child) from organization table with orderby on createddate + orgid combination.

Also I added another log table Organization_log with exact columns as Organization table and additional 'IS_DELETED' bool column.

-------------
WITH Org_TREE AS (
SELECT *, null as 'IS_DELETED', convert (varchar(4000), convert(varchar(30),CREATED_DT,126) + Org_Id) theorderby
FROM Organization WHERE PARENT_Org_ID IS NULL and case_ID='43333'
UNION ALL
SELECT a1.*, null as 'IS_DELETED', convert (varchar(4000), a2.theorderby + convert(varchar(30),a1.CREATED_DT,126) + a1.Org_Id)
FROM Organization a1 INNER JOIN Org_TREE a2 ON a1.PARENT_Org_ID = a2.Org_Id and case_ID='43333'
)
SELECT * FROM Org_TREE order by theorderby

------------------------------

I need to modify the query
1. To display the records both from the Organization table and Organization_Log table.
2. theorderby should be sorted on 'Organization Name' asc and it should follow the child order in alpha sort as well.
for eg:

aaa
==>fff
==>ggg
bbb
==> aaa
==> hhh
eee
==> ccc
==> ddd
==> fff

ANy help on how the query should be?

Thanks
Gkol

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 02/24/2014 :  11:44:14  Show Profile  Reply with Quote
I am sure, you have to do something like group by and then order by in order to get the desired sorting.

!!_(M)_!!
Go to Top of Page

gkola
Starting Member

4 Posts

Posted - 02/24/2014 :  11:53:57  Show Profile  Reply with Quote
I am new to sql programming, an example might help. Thanks.
Go to Top of Page

gkola
Starting Member

4 Posts

Posted - 02/25/2014 :  08:50:52  Show Profile  Reply with Quote
Any help?
Go to Top of Page

gkola
Starting Member

4 Posts

Posted - 02/25/2014 :  08:51:02  Show Profile  Reply with Quote
Any help?
Go to Top of Page

maunishq
Yak Posting Veteran

Canada
71 Posts

Posted - 02/25/2014 :  11:42:53  Show Profile  Reply with Quote
Do a UNION for the Org_Log_Table with the above query. And use '0' AS IS_DELETED instead of 'NULL'.
If possible please elaborate your problem.


!!_(M)_!!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 1.38 seconds. Powered By: Snitz Forums 2000