SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 self join - basic?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

intrinsic
Starting Member

2 Posts

Posted - 11/26/2011 :  23:21:16  Show Profile  Reply with Quote
Hi. I have a table that has 'duplicate' records like the following:


Name:
ID | Name | Name Type | dep ID | store ID | manager ID
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


and a name type table:

ID | Name Type
1 short
2 long
3 other


(for now I'm searching on ID's (dep, store, manager), will be searching on names as well in the future.)

Say I search on ID 2

My result set should be:


ID | Name | Long Name | entity type | dep ID | store ID | manager ID
1 def dave ed farrus manager null null 2
2 ghi geko here in dep 2 3 null


It's ok to return Store ID in record 2 as I may want to have a link to the store record in the results set as well.

but, I think I need to do a self join, but my attempts have not worked.
I also want to convert this to Linq, but I think I can just save the view in SQL and call the view through linq.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/28/2011 :  03:14:54  Show Profile  Reply with Quote
can you explain how you think you will get expected output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

intrinsic
Starting Member

2 Posts

Posted - 11/28/2011 :  14:30:38  Show Profile  Reply with Quote
Well, I asked the question first!

Is it really too difficult for everyone? Ok, here it is:

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


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?


Go to Top of Page

robvolk
Most Valuable Yak

USA
15678 Posts

Posted - 11/30/2011 :  10:53:04  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
Well, I asked the question first!
Actually you didn't. You say you think you need to do a self-join.
quote:
Is it really too difficult for everyone?
Yes it is, here's why:
  1. You post a table structure in non-DDL format, with no data types:
  2. quote:
    Name:
    ID | Name | Name Type | dep ID | store ID | manager ID
  3. You post example data that is unformatted, with no clear delineation between columns:
  4. 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
  5. 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
  6. We're not mind readers, and you're not paying us to help you.
  7. 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?
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?)

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
FROM @name
WHERE StoreID IS NOT NULL OR ManagerID IS NOT NULL
GROUP BY DepartmentID, StoreID, ManagerID
This 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
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 11/30/2011 :  13:08:34  Show Profile  Reply with Quote
quote:
Originally posted by robvolk
We're not mind readers...



You are obviously not counting Peso (or yourself)

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 11/30/2011 :  15:30:32  Show Profile  Reply with Quote
Strange...Visakh is not a mind reader today....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/01/2011 :  00:13:18  Show Profile  Reply with Quote
quote:
Originally posted by sodeep

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


Thats true
I've very little time these days to read the minds of others

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/


Edited by - visakh16 on 12/01/2011 00:13:53
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000