This should be simple
-
I have the following table structure: Company: Id (Int), Name (VarChar) Group: Id (Int), Name (VarChar) Division: Id (Int), GroupId (Int) FK, Name Company_Group: CompanyId (Int) FK, GroupId (Int) FK Team: Id (Int), DivisionId (Int) FK, Name (VarChar) I am trying to retrieve the Team Id and Name according to the CompanyId and DivisionId supplied. Note that it is entirely possible that a team may belong to a division in multiple Company's. I have tried various things but seem to get always get unwanted Teams in the results.
I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife
-
I have the following table structure: Company: Id (Int), Name (VarChar) Group: Id (Int), Name (VarChar) Division: Id (Int), GroupId (Int) FK, Name Company_Group: CompanyId (Int) FK, GroupId (Int) FK Team: Id (Int), DivisionId (Int) FK, Name (VarChar) I am trying to retrieve the Team Id and Name according to the CompanyId and DivisionId supplied. Note that it is entirely possible that a team may belong to a division in multiple Company's. I have tried various things but seem to get always get unwanted Teams in the results.
I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife
try this
select t.id,t.name,d.name,g.name,c.*
from team t
inner join division d on d.id = t.divisionid
inner join [group] g on g.id = d.groupid
inner join Company_Group cg on cg.groupid = g.id
inner join company c on c.id = cg.companyidHope it will help you
I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.
-
try this
select t.id,t.name,d.name,g.name,c.*
from team t
inner join division d on d.id = t.divisionid
inner join [group] g on g.id = d.groupid
inner join Company_Group cg on cg.groupid = g.id
inner join company c on c.id = cg.companyidHope it will help you
I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.
Sorry Blu_Boy, not quite the expected results. I may not have explained correctly, I want to get only the Teams from the supplied Company (@CompanyId) and where the supplied DivisionId (and thus referenced GroupId) maps to the GroupId in Company_Group table. This is where I'm stuck. This really should be super easy, but I'm not seeing it tonight.
I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife
-
Sorry Blu_Boy, not quite the expected results. I may not have explained correctly, I want to get only the Teams from the supplied Company (@CompanyId) and where the supplied DivisionId (and thus referenced GroupId) maps to the GroupId in Company_Group table. This is where I'm stuck. This really should be super easy, but I'm not seeing it tonight.
I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife
I may be wrong again with query.
select c.company,g.name,d.name,t.name
from Company_Group cg
inner join company c on c.id=cg.companyid
inner join [group] g on g.id=cg.groupid
inner join division d on d.id=g.id and d.groupid = cg.groupid
inner join team t on t.id = d.id
I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.
-
I may be wrong again with query.
select c.company,g.name,d.name,t.name
from Company_Group cg
inner join company c on c.id=cg.companyid
inner join [group] g on g.id=cg.groupid
inner join division d on d.id=g.id and d.groupid = cg.groupid
inner join team t on t.id = d.id
I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.
-
Thanks for your time Blue_Boy, I have found that the design is flawed and there will have to be a work-around outside of the database.
I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife