Circular Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Circular Query

Author

Topic

derrickcmg
Starting Member

3 Posts

Posted - 2009-06-25 : 14:42:34

CREATE TABLE [dbo].[Contacts](
[ContactID] [int] NOT NULL,
[Last Name] [nvarchar](50) NULL,
[First Name] [nvarchar](50) NULL,
[E-mail Address] [nvarchar](50) NULL,
[Home Phone] [nvarchar](25) NULL,
[Mobile Phone] [nvarchar](25) NULL,
[Spouse] int, // This is a circular references back to same table
[Supervisor] int // This is a circular references back to same table
)

How do I write a query to generate a report that would display names of current Contact
Spouse and Supervisor

Select Last Name, First Name, E-mail Address, Home Phone From Contacts
Inner Join Contacts ON Spouse = ContactID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-25 : 14:50:01

[code]select c1.[First Name] + ' ' + COALESCE(c1.[Last Name],'') AS Contact,
c2.[First Name] + ' ' + COALESCE(c2.[Last Name],'') AS Spouse,
c3.[First Name] + ' ' + COALESCE(c3.[Last Name],'') AS Supervisor
from dbo.Contacts c1
LEFT JOIN dbo.Contacts c2 ON c2.ContactID=c1.Spouse
INNER JOIN dbo.Contacts c3 ON c3.ContactID=c1.Supervisor
[/code]

derrickcmg
Starting Member

3 Posts

Posted - 2009-06-25 : 15:03:17

I believe this will work.
Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-26 : 14:02:17

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources