Conditional Joins to intermediate file MS Server 2012 Exp

Question

My database has 3 tables: [Groups] - information on specific groups,

CREATE TABLE [dbo].[Groups](
  [Prime_ID] [nvarchar](20) NULL,
  [Name] [nvarchar](100) NULL,   -- there are a number of other fields as well
) ON [PRIMARY]

[User_Groups] - not really a good name, because this is a list of groups each user belongs to. The group might be listed by Prime_ID or by Alias

CREATE TABLE [dbo].[User_Groups](
  [uPrime_ID] [nvarchar](20) NULL,   -- the User's Prime_ID
  [gPrime_ID] [nvarchar](20) NULL,   -- the Group Prime_ID
  [gAlias] [nvarchar](100) NULL,     -- the Group alias
) ON [PRIMARY]

[Convert] - This is an intermediate file, to Convert from an Alias to Prime_ID

CREATE TABLE [dbo].[Convert](
  [Alias] [nvarchar](100) NOT NULL,
  [Prime_ID] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_Convert] PRIMARY KEY CLUSTERED 
) ON [PRIMARY]

The problem is that the User_Groups file will have either the gPrime_ID or gAlias field filled. Occasionally it may have both filled.

The Alias field refers to the Prime_ID. i.e. Alias "GeorgeOfTheJungle" might point to Prime_ID "BQ47823"

When a row of [User_Groups] contains a gPrime_ID, it can link directly to [Groups], but when it has a gAlias it must first link to the [Convert] table to get the Prime_ID, then to the [Groups] table to get the group name, access level & etc

I have tried the method listed at "Joining Three or More Tables" http://msdn.microsoft.com/en-us/library/aa213227%28v=sql.80%29.aspx (MSDN Method), as well as that from "Conditional Joins in SQL Server" http://weblogs.sqlteam.com/jeffs/archive/2007/04/03/Conditional-Joins.aspx (Jeff's Archive Method) with no success. The MSDN method did allow me to link via the [Convert] table to [Groups] correctly OR directly to [Groups], but not both. The other method just mooned me & drove off.

MSDN Method:

SELECT g.name, g.Prime_ID, t.gPrime_ID, t.gAlias

FROM Groups g 
  INNER JOIN [Convert] cv
    ON g.Prime_ID = cv.Prime_ID 
  JOIN User_Groups t
    ON t.gAlias = cv.Alias -- JOIN User_Groups t
--   on t.gPrime_ID = g.Prime_ID
go

Jeff's Archive Method:

SELECT
  g.name, g.Prime_ID, t.gPrime_ID, t.gAlias, coalesce(g.Prime_ID, cv.Alias) AS gp
FROM
  [Groups] g
LEFT OUTER JOIN
  [User_Groups] t  ON t.gPrime_ID = g.Prime_ID
LEFT OUTER JOIN 
  [Convert] cv
  ON g.Prime_ID = cv.Prime_ID 
  JOIN User_Groups ON t.gAlias = cv.Alias
go

I'm using MS Server 2012 Express

score 1 · Accepted Answer · answered Jan 21 '13 at 09:22

This query should try to link Groups and User_Groups either directly, or through the intermediate table:

select  *
from    Groups g
left join
        Convert c
on      c.Prime_ID = g.Prime_ID
left join
        User_Groups ug
on      ug.gPrime_ID = g.Prime_ID
        or ug.gAlias = g.gAlias

Conditional Joins to intermediate file MS Server 2012 Exp

1 Answers1