Union parent child records - 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

Union parent child records

Author

Topic

gkola
Starting Member

4 Posts

Posted - 2014-02-24 : 11:29:48

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

71 Posts

Posted - 2014-02-24 : 11:44:14

I am sure, you have to do something like group by and then order by in order to get the desired sorting.

!!_(M)_!!

gkola
Starting Member

4 Posts

Posted - 2014-02-24 : 11:53:57

I am new to sql programming, an example might help. Thanks.

gkola
Starting Member

4 Posts

Posted - 2014-02-25 : 08:50:52

Any help?

gkola
Starting Member

4 Posts

Posted - 2014-02-25 : 08:51:02

Any help?

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-02-25 : 11:42:53

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)_!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources