Friday, March 23, 2012

Right join not working when joining 3 tables

Have 2 tables that are joined by a 3rd. I want to get all the date in both
tables whether they are connected or not.
There can be many policies per role. RolePolicies is the table that
connects the two.
CREATE TABLE [dbo].[Roles] (
[RoleID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Roles] ADD
CONSTRAINT [PK_Roles_1] PRIMARY KEY CLUSTERED
(
[RoleID]
) ON [PRIMARY]
CREATE TABLE [dbo].[Policies] (
[PolicyID] [int] IDENTITY (1, 1) NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Policies] ADD
CONSTRAINT [PK_Policies] PRIMARY KEY CLUSTERED
(
[PolicyID]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[RolePolicies] (
[RoleID] [int] NULL ,
[PolicyID] [int] NULL
) ON [PRIMARY]
GO
INSERT Roles (Description) VALUES ('Manager')
INSERT Roles (Description) VALUES ('Director')
INSERT Roles (Description) VALUES ('User')
INSERT Policies (Description) VALUES ('Add')
INSERT Policies (Description) VALUES ('Edit')
INSERT Policies (Description) VALUES ('Delete')
INSERT RolePolicies (RoleID,PolicyID) VALUES (1,1)
INSERT RolePolicies (RoleID,PolicyID) VALUES (1,2)
INSERT RolePolicies (RoleID,PolicyID) VALUES (2,1)
INSERT RolePolicies (RoleID,PolicyID) VALUES (2,2)
SELECT *
FROM RolePolicies rp
RIGHT JOIN Roles r ON (rp.RoleID = r.RoleID)
RIGHT JOIN Policies p ON (rp.PolicyID = p.PolicyID)
RoleID PolicyID RoleID Description PolicyID
Description
-- -- -- -- -- --
--
1 1 1 Manager
1 Add
2 1 2 Director
1 Add
1 2 1 Manager
2 Edit
2 2 2 Director
2 Edit
NULL NULL NULL NULL 3
Delete
Here the last RIGHT JOIN works fine and we get the Delete, even though it
isn't in RolePolicies. But why don't we get 'User' from the 1st RIGHT JOIN?
If I do it this way:
SELECT *
FROM RolePolicies rp
RIGHT JOIN Roles r ON (rp.RoleID = r.RoleID)
I get the extra line ("User").
RoleID PolicyID RoleID Description
-- -- -- --
1 1 1 Manager
1 2 1 Manager
2 1 2 Director
2 2 2 Director
NULL NULL 3 User
How do I get both the "User" as well as the "Add"?
Thanks,
TomTshad,
Is it a LEFT JOIN that you want instead?
If not what should the resultset look like?
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e$NdcS3yFHA.3756@.tk2msftngp13.phx.gbl...
> Have 2 tables that are joined by a 3rd. I want to get all the date in
> both tables whether they are connected or not.
> There can be many policies per role. RolePolicies is the table that
> connects the two.
> CREATE TABLE [dbo].[Roles] (
> [RoleID] [int] IDENTITY (1, 1) NOT NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Roles] ADD
> CONSTRAINT [PK_Roles_1] PRIMARY KEY CLUSTERED
> (
> [RoleID]
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[Policies] (
> [PolicyID] [int] IDENTITY (1, 1) NOT NULL ,
> [Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Policies] ADD
> CONSTRAINT [PK_Policies] PRIMARY KEY CLUSTERED
> (
> [PolicyID]
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[RolePolicies] (
> [RoleID] [int] NULL ,
> [PolicyID] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT Roles (Description) VALUES ('Manager')
> INSERT Roles (Description) VALUES ('Director')
> INSERT Roles (Description) VALUES ('User')
> INSERT Policies (Description) VALUES ('Add')
> INSERT Policies (Description) VALUES ('Edit')
> INSERT Policies (Description) VALUES ('Delete')
> INSERT RolePolicies (RoleID,PolicyID) VALUES (1,1)
> INSERT RolePolicies (RoleID,PolicyID) VALUES (1,2)
> INSERT RolePolicies (RoleID,PolicyID) VALUES (2,1)
> INSERT RolePolicies (RoleID,PolicyID) VALUES (2,2)
> SELECT *
> FROM RolePolicies rp
> RIGHT JOIN Roles r ON (rp.RoleID = r.RoleID)
> RIGHT JOIN Policies p ON (rp.PolicyID = p.PolicyID)
> RoleID PolicyID RoleID Description PolicyID
> Description
> -- -- -- -- -- --
--
> 1 1 1 Manager 1
> Add
> 2 1 2 Director 1
> Add
> 1 2 1 Manager 2
> Edit
> 2 2 2 Director 2
> Edit
> NULL NULL NULL NULL 3 Delete
> Here the last RIGHT JOIN works fine and we get the Delete, even though it
> isn't in RolePolicies. But why don't we get 'User' from the 1st RIGHT
> JOIN?
> If I do it this way:
> SELECT *
> FROM RolePolicies rp
> RIGHT JOIN Roles r ON (rp.RoleID = r.RoleID)
> I get the extra line ("User").
> RoleID PolicyID RoleID Description
> -- -- -- --
> 1 1 1 Manager
> 1 2 1 Manager
> 2 1 2 Director
> 2 2 2 Director
> NULL NULL 3 User
> How do I get both the "User" as well as the "Add"?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23fgZcZ3yFHA.2848@.TK2MSFTNGP15.phx.gbl...
> Tshad,
> Is it a LEFT JOIN that you want instead?
>
No.
That is why I showed the 2nd statement which just takes away the Join that
worked. When it was gone it worked correctly and showed the role "User".

> If not what should the resultset look like?
It should look the same as the 1st result set with one more row showing the
role "User":
RoleID PolicyID RoleID Description PolicyID
Description
-- -- -- -- -- --
--
1 1 1 Manager
1 Add
2 1 2 Director
1 Add
1 2 1 Manager
2 Edit
2 2 2 Director
2 Edit
NULL NULL NULL NULL 3
Delete
NULL NULL 3 User NULL
NULL
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e$NdcS3yFHA.3756@.tk2msftngp13.phx.gbl...
>|||tshad,
Try using FULL JOIN:
SELECT *
FROM ROLES R FULL JOIN ROLEPOLICIES RP
ON R.ROLEID = RP.ROLEID
FULL JOIN POLICIES P
ON RP.POLICYID = P.POLICYID
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ehx$oc4yFHA.3864@.TK2MSFTNGP12.phx.gbl...
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23fgZcZ3yFHA.2848@.TK2MSFTNGP15.phx.gbl...
> No.
> That is why I showed the 2nd statement which just takes away the Join that
> worked. When it was gone it worked correctly and showed the role "User".
>
> It should look the same as the 1st result set with one more row showing
> the role "User":
> RoleID PolicyID RoleID Description PolicyID
> Description
> -- -- -- -- -- --
--
> 1 1 1 Manager 1
> Add
> 2 1 2 Director 1
> Add
> 1 2 1 Manager 2
> Edit
> 2 2 2 Director 2
> Edit
> NULL NULL NULL NULL 3 Delete
> NULL NULL 3 User NULL
> NULL
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uKlnH94yFHA.3316@.TK2MSFTNGP10.phx.gbl...
> tshad,
> Try using FULL JOIN:
> SELECT *
> FROM ROLES R FULL JOIN ROLEPOLICIES RP
> ON R.ROLEID = RP.ROLEID
> FULL JOIN POLICIES P
> ON RP.POLICYID = P.POLICYID
That worked.
I also could do it with my statement (which really is the same as yours) by
replacing both "RIGHT JOIN"s with "FULL JOIN"s, as you suggested.
Not sure why the outside RIGHT JOIN would work and not the inside one.
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ehx$oc4yFHA.3864@.TK2MSFTNGP12.phx.gbl...
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:uKlnH94yFHA.3316@.TK2MSFTNGP10.phx.gbl...
> tshad,
> Try using FULL JOIN:
> SELECT *
> FROM ROLES R FULL JOIN ROLEPOLICIES RP
> ON R.ROLEID = RP.ROLEID
> FULL JOIN POLICIES P
> ON RP.POLICYID = P.POLICYID
This works well, but how do I tell it not to display rows that have Nulls in
the result set in certain columns.
I actually used this with 3 tables and it works fine, but I didn't want to
display rows that Nulls in 2 of the columns of the result set.
This is a little confusing, I know.
But if you take the statement and change it to:
SELECT *
FROM ROLES R FULL JOIN ROLEPOLICIES RP
ON R.ROLEID = RP.ROLEID
FULL JOIN POLICIES P
WHEN P.POLICYID <> NULL
ON RP.POLICYID = P.POLICYID
You will get no results.
This makes sense, because all the PolicyID records in the actual table have
something in them.
But in the full join, there can be a Null in the PolicyID if the there is a
record in Policies, but not in RolePolicies.
This would be a ridiculous statement in this example, but I would like it to
display all the rows that have no nulls.
For example,
RoleID PolicyID RoleID Description PolicyID
Description
-- -- -- -- -- --
--
1 1 1 Manager
1 Add
2 1 2 Director
1 Add
1 2 1 Manager
2 Edit
2 2 2 Director
2 Edit
NULL NULL NULL NULL 3
Delete
NULL NULL 3 User NULL
NULL
I want to test for either PolicyID or RoleID, after the result set is
created.
In my other, Select - I want to test for both being NULL.
The statement would be something like:
SELECT *
FROM ROLES R FULL JOIN ROLEPOLICIES RP
ON R.ROLEID = RP.ROLEID
FULL JOIN POLICIES P
WHEN P.POLICYID <> NULL AND R.ROLEID <> NULL
ON RP.POLICYID = P.POLICYID
In my example, I should get the same result set, but I actually get no
results.
Thanks,
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ehx$oc4yFHA.3864@.TK2MSFTNGP12.phx.gbl...
that
"User".
>
-- -- -- -- -- --
--
,
>
--
>

No comments:

Post a Comment