quote:Actually you didn't. You say you think you need to do a self-join.
Well, I asked the question first!
quote:Yes it is, here's why:
Is it really too difficult for everyone?
- You post a table structure in non-DDL format, with no data types:
ID | Name | Name Type | dep ID | store ID | manager ID
- You post example data that is unformatted, with no clear delineation between columns:
1 abc 1 1 null null
2 Alberta Biz corp 2 1 null null
3 def 1 null null 2
4 dave ed farrus 2 null null 2
5 ghi 1 2 3 null
6 geko here in 2 2 3 null
- You then post a SQL query that:
- Uses different column names from your original post
- Won't compile due to syntax errors
- Misspells one of the tables
- Uses 3-part names when 2 parts would do just fine (and make it easier to read)
- Includes tables that you didn't document
- And from what you've written, aren't even necessary to give the results you're asking for
- And won't return data based on the Nulls and JOIN type you've chosen
quote:We're not mind readers, and you're not paying us to help you.You scoff at a legitimate question from a SQL Server MVP and long-time member of SQLTeam, who definitely understands SQL Server, and has helped thousands of people before you, but can't figure out your question from the "information" you've provided.
SELECT DISTINCT domain.Names.Id, domain.Names.Name, Names_1.Name AS shortname, domain.Names.departmentid, domain.Names.storeid, domain.Names.managerid,
FROM domain.Names INNER JOIN
domain.Departments ON domain.Names.departmentid = domain.Departments.Id INNER JOIN
domain.Stores ON domain.Names.storeid=
domain.Stores.Id INNER JOIN
domain.Managers ON domain.Names.managerid=
domain.Managers.Id INNER JOIN
domain.Names AS Names_1 ON
Names_1.departmentid = domain.Departments.Id AND
Names_1.storeid= domain.Stores.Id AND
Names_1.managerid = domain.Mangers.ID AND
Names_1.Name <> domain.Names.Name AND
Names_1.NameTypeId = 1
- Which is not surprising, as he probably has better things to do, and isn't bored at work and annoyed enough to respond with this level of detail, like I am.
quote:I don't know, since you didn't post a query with that JOIN, or explain what "didn't work" means. (Didn't return results? Didn't return the correct results? Didn't run at all due to syntax error?)
I tried to link on NameTypes table, but it didn't work, but linking on departments, stores, and managers worked. Why doesn't it work when I Join on Nametypes table?
Based on what I think you want, the following example provides the results you posted:
DECLARE @name TABLE(ID INT NOT NULL PRIMARY KEY, Name VARCHAR(50) NOT NULL, NameTypeID INT, DepartmentID INT, storeID INT, ManagerID INT)
INSERT @name SELECT 1,'abc',1,1,NULL,NULL UNION ALL SELECT
2,'Alberta Biz corp',2,1,NULL,NULL UNION ALL SELECT
3,'def',1,NULL,NULL,2 UNION ALL SELECT
4,'dave ed farrus',2,NULL,NULL,2 UNION ALL SELECT
5,'ghi',1,2,3,NULL UNION ALL SELECT
6,'geko here in',2,2,3,NULL
SELECT ROW_NUMBER() OVER (ORDER BY StoreID, ManagerID) ID,
MAX(CASE WHEN NameTypeID=1 THEN Name END) Name,
MAX(CASE WHEN NameTypeID=2 THEN Name END) LongName,
DepartmentID, StoreID, ManagerID
WHERE StoreID IS NOT NULL OR ManagerID IS NOT NULL
GROUP BY DepartmentID, StoreID, ManagerIDThis is a simple pivot/cross-tab query. This also doesn't join to the NameTypes table, since it's really not necessary for only 2 name options. If you need to include more, and/or need to have the names pivoted dynamically, then you'll need to rewrite it.
Please read this and follow it next time you'd like an answer: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx