Re: How to specify the OUTER JOIN ?
- From: Gert-Jan Strik <sorry@xxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 15 Jul 2009 22:29:41 +0200
Savvoulidis,
If I understand you correctly, this is what you want:
SELECT A.A1, C1.TRANSLATION
, A.A2, C2.TRANSLATION
, A.A3, C3.TRANSLATION
, A.A4
FROM A
LEFT JOIN B B1 ON B1.B1=A.A1
LEFT JOIN C C1 ON C1.C1=B1.B1 AND C1.LANG=@lang
LEFT JOIN B B2 ON B2.B1=A.A2
LEFT JOIN C C2 ON C2.C1=B2.B1 AND C2.LANG=@lang
LEFT JOIN B B3 ON B3.B1=A.A3
LEFT JOIN C C3 ON C3.C1=B3.B1 AND C3.LANG=@lang
Now in this case, if you really don't need any columns from B, then it
doesn't add any value to have the table "in between", and you could
simplify the above query to:
SELECT A.A1, C1.TRANSLATION
, A.A2, C2.TRANSLATION
, A.A3, C3.TRANSLATION
, A.A4
FROM A
LEFT JOIN C C1 ON C1.C1=A.A1 AND C1.LANG=@lang
LEFT JOIN C C2 ON C2.C1=A.A2 AND C2.LANG=@lang
LEFT JOIN C C3 ON C3.C1=A.A3 AND C3.LANG=@lang
The essential part is to have the predicate "LANG=@lang" in the ON
clause of the outer join, and not in the WHERE clause. If you put it in
the WHERE clause, it basically negates the entire outer join.
--
Gert-Jan
SQL Server MVP
Savvoulidis Iordanis wrote:
.
Below, I give a sample DB of the problem I have in SQL Server. Tables A and
C, reference table B, where the table of interest is A. Table C contains the
translations of table B. Columns A2 and A3 are foreign keys to table B, where
A3 can be NULL (which is my problem).
I need to create a SQL SERVER stored procedure with a SQL SELECT, using all
records and columns from A, along with their corresponding translations from
table C for a given language even if A3 is null. Maybe an OUTER JOIN better
that mine is needed, but I'm stuck. Everything goes OK with the outer join,
only if I get columns from tables A and B. But I want columns from A and C
only.
Here is the SQL to create the tables, records and my stored procedure
(execute what follows, as is):
/*************************************************************************************************/
/****** Object: Table [dbo].[B] Script Date: 07/13/2009 17:30:48 ******/
/*************************************************************************************************/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[B](
[B1] [int] NOT NULL,
[B2] [nchar](10) NOT NULL,
[B3] [nchar](10) NOT NULL,
CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED
(
[B1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/*************************************************************************************************/
/****** Object: Table [dbo].[A] Script Date: 07/13/2009 17:31:21 ******/
/*************************************************************************************************/
CREATE TABLE [dbo].[A](
[A1] [int] NOT NULL,
[A2] [int] NOT NULL,
[A3] [int] NULL,
[A4] [nchar](10) NOT NULL,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[A1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[A] WITH CHECK ADD CONSTRAINT [FK_A_B] FOREIGN KEY([A2])
REFERENCES [dbo].[B] ([B1])
GO
ALTER TABLE [dbo].[A] CHECK CONSTRAINT [FK_A_B]
GO
ALTER TABLE [dbo].[A] WITH CHECK ADD CONSTRAINT [FK_A_B1] FOREIGN KEY([A3])
REFERENCES [dbo].[B] ([B1])
GO
ALTER TABLE [dbo].[A] CHECK CONSTRAINT [FK_A_B1]
GO
/*************************************************************************************************/
/****** Object: Table [dbo].[C] Script Date: 07/13/2009 17:35:40 ******/
/*************************************************************************************************/
CREATE TABLE [dbo].[C](
[C1] [int] NOT NULL,
[LANG] [nchar](10) NOT NULL,
[TRANSLATION] [nchar](20) NOT NULL,
CONSTRAINT [PK_C] PRIMARY KEY CLUSTERED
(
[C1] ASC,
[LANG] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[C] WITH CHECK ADD CONSTRAINT [FK_C_B] FOREIGN KEY([C1])
REFERENCES [dbo].[B] ([B1])
GO
ALTER TABLE [dbo].[C] CHECK CONSTRAINT [FK_C_B]
GO
/*************************************************************************************************/
/****** Object: StoredProcedure [dbo].[_SQL_SELECT] Script Date:
07/13/2009 17:37:38 ******/
/*************************************************************************************************/
CREATE PROCEDURE [dbo].[_SQL_SELECT]
(@lang nchar(10))
AS
SELECT A.A1, A.A2, A.A3, A.A4, C.TRANSLATION, C_1.TRANSLATION AS Expr1
FROM C AS C_1 INNER JOIN
B AS B_1 ON C_1.C1 = B_1.B1 RIGHT OUTER JOIN
A INNER JOIN
B ON A.A2 = B.B1 INNER JOIN
C ON B.B1 = C.C1 ON B_1.B1 = A.A3
WHERE (C_1.LANG = @lang) AND (C.LANG = @lang)
ORDER BY A.A1
GO
insert into B values(1, '1st_B2', '1st_B3')
insert into B values(2, '2nd_B2', '2nd_B3')
insert into B values(3, '3rd_B2', '3rd_B3')
insert into B values(4, '4th_B2', '4th_B3')
insert into A values(1, 1, null, '1st_A4')
insert into A values(2, 2, 3, '2nd_A4')
insert into A values(3, 1, 4, '3rd_A4')
insert into A values(4, 3, null, '4th_A4')
insert into A values(5, 1, 2, '5th_A4')
insert into C values(1, 'ENG', '1st_ENG')
insert into C values(1, 'GR', '1st_GR')
insert into C values(2, 'ENG', '2nd_ENG')
insert into C values(2, 'GR', '2nd_GR')
insert into C values(3, 'ENG', '3rd_ENG')
insert into C values(3, 'GR', '3rd_GR')
insert into C values(4, 'ENG', '4th_ENG')
insert into C values(4, 'GR', '4th_GR')
GO
- Follow-Ups:
- Re: How to specify the OUTER JOIN ?
- From: Savvoulidis Iordanis
- Re: How to specify the OUTER JOIN ?
- References:
- How to specify the OUTER JOIN ?
- From: Savvoulidis Iordanis
- How to specify the OUTER JOIN ?
- Prev by Date: Re: Trim extra spaces in concatenated values
- Next by Date: failover noob questions why DB owner change doesn't effect DB mirror
- Previous by thread: Re: How to specify the OUTER JOIN ?
- Next by thread: Re: How to specify the OUTER JOIN ?
- Index(es):
Relevant Pages
|
