quote:

---

Well, I asked the question first!

---

quote:

---

Is it really too difficult for everyone?

---

1. You post a table structure in non-DDL format, with no data types:

quote:

---

Name:
ID | Name | Name Type | dep ID | store ID | manager ID

---

2. You post example data that is unformatted, with no clear delineation between columns:

quote:

---

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

---

3. You then post a SQL query that:
1. Uses different column names from your original post
2. Won't compile due to syntax errors
3. Misspells one of the tables
4. Uses 3-part names when 2 parts would do just fine (and make it easier to read)
5. 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:

---

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

---

4. We're not mind readers, and you're not paying us to help you.
5. 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.
• 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 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?

---

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
FROM @name
WHERE StoreID IS NOT NULL OR ManagerID IS NOT NULL
GROUP BY DepartmentID, StoreID, ManagerID

quote:

---

Originally posted by robvolk
We're not mind readers...

---

quote:

---

Originally posted by sodeep

Strange...Visakh is not a mind reader today....

---