April 11, 2013 at 11:30 am
//EDIT: I've posted in the wrong board, I should have posted in the General Board, ut I don't know how to move this!
I'm fairly green with SQL (am still learning on the job!) so please accept my apologies if this both a confused question as well as a very basic question.
I have a table that I'll simplify to have justthe following columns:
CompanyName (string), CompanyID (Guid), Heirarchy (int), ParentCompanyID (Guid)
The ParentCompanyID I suppose is self-referential in that if the Company is a sub-division of a parent company it will have say an Heirarchy =1 and the CompanyID of the Parent Company held in the same Table.
I have been trying to write a Select stmt to show the Parent CompanyName instead of the ParentCompanyID:
Select CompanyName, Heirarchy, [substitute ParentCompanyID for Parent CompanyName]
From Company
Order by CompanyName
If I use a simple Where clause of Where ParentCompanyID = CompanyID I land up filtering down to only companies that have SubDivisions. This has occupied my entire afternoon and I have gotten nowhere so am turning to the forums in the hope I can be put of my misery!
Hopefully someone could point me in the right direction, unfortunately I can't change the schema as it is form the company's CRM system.
April 11, 2013 at 11:55 am
Something like this should work for you
SELECT c1.CompanyName
,c1.CompanyID
,c1.Heirarchy
,c2.CompanyName
FROM Company c1
LEFT JOIN Company c2 ON c1.parentCompanyID = c2.CompanyID
April 11, 2013 at 3:19 pm
The above suggestion works great for a hierarchy one level deep.
You may also want to see if you need a recursive query, something like this:
http://msdn.microsoft.com/en-nz/library/ms186243%28v=sql.100%29.aspx
April 11, 2013 at 4:07 pm
Hi,
Thanks for replying, I must apologise I think I have simplified far too much and thus not covered a point that causes me a problem with the solution you provided sadly.
I am actually already doing an inner join with another table called Opportunities where I call some values from that table, again for the sake of simplicity I will only list 2 or 3 fields.
Opportunity Table:
OppName(string), OppID(Guid), Opp_Status(String)
This is relate to the Company table via the Company_Opportunity table:
CompanyID(Guid), OppID(Guid)
So my actual query currently looks like:
Select CompanyName, Heirarchy, OppName, Opp_Status
From Company C Inner Join
Company_Opportunity CO on CO.CompanyID = C.CompanyID Inner Join
Opportunity O on CO.OppID = O.OppID
Order By C.Company
When I insert the offered solution into the select stmt after Opp_Status, the query only brings back Companies that are Divisions with a Heriarchy =1.
I apologise for not fully fleshing out my problem in the initial question, but I didn't think it would add to the problem! When I run the solution by its self without the other inner join it works perfectly but I need to bring in the Opps data.
Thanks for your patience in advance.
April 11, 2013 at 4:34 pm
April 12, 2013 at 6:06 am
Hi sorry about that, guess who didn't read the forum guidelines! *blush*
I have changed the schema for this example from using Guids to uising INT for IDs
if OBJECT_ID('Test..Company','U')is not null
drop table Company
--Create Test Table
create table Company(
CompanyID INT IDENTITY(1,1) Primary key clustered,
CompanyName varchar(10),
Heirarchy int,
ParentCompanyID int
)
if OBJECT_ID('Test..Opportunity','U')is not null
drop table Opportunity
create table Opportunity(
OppID Int identity(1,1) primary key clustered,
OppName varchar(10),
Opp_status varchar(10)
)
if OBJECT_ID('Test..Company_Opportunity','U')is not null
drop table Company_Opportunity
create table Company_Opportunity(
CompanyID int,
OppID int
)
--Create Data
Set identity_insert Company ON
Insert into Company
(CompanyID,CompanyName,Heirarchy,ParentCompanyID)
Select '1','Company1',0,'' UNION all
Select '2','Company2',0,'' Union all
Select '3','Div1',1,1 Union all
Select '4','Company3',0,'' Union all
Select '5','Div2',1,2 Union all
Select '6','Company4',0,''
Set Identity_Insert Company off
Set identity_insert Opportunity ON
Insert into Opportunity (OppID,OppName,Opp_status)
Select '1','Opp1','Open' Union all
Select '2','Opp2','Lost' Union all
Select '3','Opp3','Won' Union all
Select '4','Opp4','Open' Union all
Select '5','Opp5','Won' Union all
Select '6','Opp6','Open' Union all
Select '7','Opp7','Lost'
Set Identity_Insert Opportunity on
Insert into Company_Opportunity
(CompanyID,OppID)
Select 1,1 Union all
Select 2,2 Union all
Select 3,3 Union all
Select 4,4 Union all
Select 5,5 Union all
Select 6,6 Union all
Select 1,7
April 12, 2013 at 7:38 am
;WITH Resolver AS (
SELECT [Level] = 1, CompanyID, CompanyName, Heirarchy, ParentCompanyID,
Hpath = CAST(CompanyID AS VARCHAR(50))
FROM Company
WHERE ParentCompanyID = 0
UNION ALL
SELECT [Level] = r.[Level]+1, c.CompanyID, c.CompanyName, c.Heirarchy, c.ParentCompanyID,
Hpath = CAST(r.Hpath + ' | ' + CAST(c.CompanyID AS VARCHAR(50)) AS VARCHAR(50))
FROM Resolver r
INNER JOIN Company c
ON c.ParentCompanyID = r.CompanyID
)
SELECT
c.[Level], c.Hpath,
c.CompanyID, c.CompanyName, c.Heirarchy, c.ParentCompanyID,
o.OppName, o.Opp_status
FROM Resolver c
LEFT JOIN Company_Opportunity co ON co.CompanyID = c.CompanyID
LEFT JOIN Opportunity o ON o.OppID = co.OppID
ORDER BY c.[Level], c.CompanyID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 12, 2013 at 9:17 am
ChrisM,
Thanks for that, I've got to be honest It's going to take me sometime to get to grips with that and understand each line in full!
I've run it but I have one problem with it in that it does not show the name of the Parent Company for the Div companies. Ideally what I was hoping to do was show something like you results, but to also if they are a Div company (hierarchy >0) the name of the Parent Company.
April 15, 2013 at 1:32 am
Something like this?
;WITH Resolver AS (
SELECT [Level] = 1, CompanyID, CompanyName, Heirarchy, ParentCompanyID,
Hpath = CAST(CompanyID AS VARCHAR(50)),
ParentCompanyNames = CAST(NULL AS VARCHAR(100))
FROM Company
WHERE ParentCompanyID = 0
UNION ALL
SELECT [Level] = r.[Level]+1, c.CompanyID, c.CompanyName, c.Heirarchy, c.ParentCompanyID,
Hpath = CAST(r.Hpath + ' | ' + CAST(c.CompanyID AS VARCHAR(50)) AS VARCHAR(50)),
ParentCompanyNames = CAST(ISNULL(r.ParentCompanyNames + ' | ','') + r.CompanyName AS VARCHAR(100))
FROM Resolver r
INNER JOIN Company c
ON c.ParentCompanyID = r.CompanyID
)
SELECT
c.[Level], c.Hpath, c.ParentCompanyNames,
c.CompanyID, c.CompanyName, c.Heirarchy, c.ParentCompanyID,
o.OppName, o.Opp_status
FROM Resolver c
LEFT JOIN Company_Opportunity co ON co.CompanyID = c.CompanyID
LEFT JOIN Opportunity o ON o.OppID = co.OppID
ORDER BY c.[Level], c.CompanyID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 15, 2013 at 5:32 am
That's it! Could I pester you a little about this query?
Where you have at the start With Resolver...are you creating a temp table? There is quite a bit in this query that I need to study to be honest! When you Union the second Select
SELECT [Level] = r.[Level]+1, c.CompanyID, c.CompanyName, c.Heirarchy, c.ParentCompanyID,
Hpath = CAST(r.Hpath + ' | ' + CAST(c.CompanyID AS VARCHAR(50)) AS VARCHAR(50)),
ParentCompanyNames = CAST(ISNULL(r.ParentCompanyNames + ' | ','') + r.CompanyName AS VARCHAR(100))
FROM Resolver r
INNER JOIN Company c
ON c.ParentCompanyID = r.CompanyID
How are you able to refer back to Resolver within it's own definition? Is this recursion TSQL style?
April 15, 2013 at 5:58 am
Yep, this is a recursive CTE, and this is one of the few problems where it's use would be recommended. I'll fish around for a reference or two...
Exploring Recursive CTEs by Example[/url]
Edit: added reference.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 15, 2013 at 7:48 am
Fantastic, that link is perfect! Thanks ever so much, seems to me the more I learn about SQL I realise the less I actually know! I got so lost reading the MSDN literature on CTE and recursive calls were almost incomprehensible for a beginner like me!
April 15, 2013 at 8:25 am
vgargav (4/15/2013)
Fantastic, that link is perfect! Thanks ever so much, seems to me the more I learn about SQL I realise the less I actually know! I got so lost reading the MSDN literature on CTE and recursive calls were almost incomprehensible for a beginner like me!
Heh you're welcome, thank you for the feedback. I've been messing with SQL Server since about '96 and I learned a ton of new stuff today 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply