Outer Apply ? - 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

New to SQL Server Programming

Outer Apply ?

Author

Topic

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2012-05-31 : 14:08:09

Hello

My query is below. There is a table x_seansogrencileri. it have columns like x_id, x_seansid, x_age . x_seansid connect to x_id of x_seans table. x_seansogrencileri can have a lot of records. I should get x_seans while i look at x_age for first record(id).

if x_seansturu is A, i need x_seans betwen +2 and -2 x_age

if x_seansturu is B, i need x_seans equal x_age.

Can you type sql for me ?
SELECT
x_seansprogrami.x_baslangicsaati as x_baslangicsaati,
x_seansprogrami.x_bitissaati as x_bitissaati,
x_seansprogrami.x_gun as x_gun,
x_seansprogrami.x_seans as x_seans,
x_seansprogrami.x_egitmen as x_egitmen
FROM x_seansprogrami
OUTER APPLY (SELECT TOP 1 x_id, x_adi FROM x_seansegitmenleri WHERE x_seansprogrami.x_egitmen = x_seansegitmenleri.x_adi ) AS x_seansegitmenleri
OUTER APPLY (SELECT TOP 1 x_id, x_adi, x_seansturu, x_seansturadi, x_ogrencisayisi FROM x_seans WHERE x_seansprogrami.x_seans = x_seans.x_adi ) AS x_seans

WHERE (NOT (x_seansprogrami.x_id IS NULL))
--------------------------
--------------------------
---------------------------
I should add below query to my query. My query is at up. How can i do with outer apply ?
SELECT x_seansogrencileri.x_yas
FROM x_seansprogrami INNER JOIN
x_seans ON x_seansprogrami.x_seans = x_seans.x_adi INNER JOIN
x_seansogrencileri ON x_seans.x_id = x_seansogrencileri.x_seansid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 15:26:00

nobody will be able to understand what you want seeing above query. Post some sample data from tables and explain what you want as output

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

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2012-05-31 : 16:25:15

Table x_seansprogrami
x_baslangicsaati x_bitissaati x_gun x_seans x_egitmen
11:00 12:00 Monday Class A Teacher A
11:00 12:00 Monday Class B Teacher B
Table x_seans
x_id x_adi(x_seans of x_seansprogrami table) x_seansturu
1 Class A A
2 Class B B

Table x_seansogrencileri
x_id x_age x_seansid(x_id of x_seans table)
1 10 1 (x_seansturu is A at x_seans table)
2 12 1
3 13 1
4 11 2 (x_seansturu is B at x_seans table)
5 9 2

I need below Results
When i set 12 or 11 or 10 or 9 or 8 as age and x_seansturu is A, I need result below. Because of First x_age is 10 at x_seansogrencileri table. So result should be Below due to between +2 and -2 x_age. If i set 13 or 7 as age, my result should be empty.
x_baslangicsaati x_bitissaati x_gun x_seans x_egitmen
11:00 12:00 Monday Class A Teacher A

When i set 11 as age and x_seansturu is Bi i need result below. Because of first record x_age is 11 at x_seansogrencileri table. So result should be Below due to age equal. no +2 and -2. If i set 10 or 9 as age, my result should be empty.
x_baslangicsaati x_bitissaati x_gun x_seans x_egitmen
11:00 12:00 Monday Class B Teacher B

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-31 : 16:41:17

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2012-05-31 : 17:29:57

Hello Again
x_yas means age at below.

USE [getup]
GO
/****** Object: Table [dbo].[x_seansprogrami] Script Date: 06/01/2012 00:28:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[x_seansprogrami](
[x_id] [int] IDENTITY(1,1) NOT NULL,
[x_baslangicsaati] [smalldatetime] NULL,
[x_bitissaati] [smalldatetime] NULL,
[x_gun] [nvarchar](150) NULL,
[x_seans] [nvarchar](150) NULL,
[x_egitmen] [nvarchar](150) NULL,
CONSTRAINT [PK_x_seminerprogrami] PRIMARY KEY CLUSTERED
(
[x_id] 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
SET IDENTITY_INSERT [dbo].[x_seansprogrami] ON
INSERT [dbo].[x_seansprogrami] ([x_id], [x_baslangicsaati], [x_bitissaati], [x_gun], [x_seans], [x_egitmen]) VALUES (1, CAST(0x00000544 AS SmallDateTime), CAST(0x00000544 AS SmallDateTime), N'Monday', N'Class A', N'Teacher A')
INSERT [dbo].[x_seansprogrami] ([x_id], [x_baslangicsaati], [x_bitissaati], [x_gun], [x_seans], [x_egitmen]) VALUES (2, CAST(0x00000544 AS SmallDateTime), CAST(0x00000544 AS SmallDateTime), N'Monday', N'Class B', N'Teacher B')
SET IDENTITY_INSERT [dbo].[x_seansprogrami] OFF
/****** Object: Table [dbo].[x_seansogrencileri] Script Date: 06/01/2012 00:28:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[x_seansogrencileri](
[x_id] [int] IDENTITY(1,1) NOT NULL,
[x_seansid] [int] NULL,
[x_ogrenciid] [int] NULL,
[x_kod] [nvarchar](50) NULL,
[x_adi] [nvarchar](100) NULL,
[x_yas] [int] NULL,
[x_baslangictarihi] [smalldatetime] NULL,
[x_bitistarihi] [smalldatetime] NULL,
CONSTRAINT [PK_x_seansogrencileri] PRIMARY KEY CLUSTERED
(
[x_id] 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
SET IDENTITY_INSERT [dbo].[x_seansogrencileri] ON
INSERT [dbo].[x_seansogrencileri] ([x_id], [x_seansid], [x_ogrenciid], [x_kod], [x_adi], [x_yas], [x_baslangictarihi], [x_bitistarihi]) VALUES (1, 1, 7, N'1', N'Ali Sahin', 10, CAST(0xA02D0000 AS SmallDateTime), CAST(0x47560000 AS SmallDateTime))
INSERT [dbo].[x_seansogrencileri] ([x_id], [x_seansid], [x_ogrenciid], [x_kod], [x_adi], [x_yas], [x_baslangictarihi], [x_bitistarihi]) VALUES (2, 1, 8, N'2', N'Yeliz', 12, CAST(0xA03F0000 AS SmallDateTime), CAST(0x47560000 AS SmallDateTime))
INSERT [dbo].[x_seansogrencileri] ([x_id], [x_seansid], [x_ogrenciid], [x_kod], [x_adi], [x_yas], [x_baslangictarihi], [x_bitistarihi]) VALUES (6, 1, 10, N'4', N'Kemal', 13, CAST(0x9FCB0000 AS SmallDateTime), CAST(0x47560000 AS SmallDateTime))
INSERT [dbo].[x_seansogrencileri] ([x_id], [x_seansid], [x_ogrenciid], [x_kod], [x_adi], [x_yas], [x_baslangictarihi], [x_bitistarihi]) VALUES (7, 2, 11, N'5', N'Onur', 11, CAST(0x9FEB0000 AS SmallDateTime), CAST(0x47560000 AS SmallDateTime))
INSERT [dbo].[x_seansogrencileri] ([x_id], [x_seansid], [x_ogrenciid], [x_kod], [x_adi], [x_yas], [x_baslangictarihi], [x_bitistarihi]) VALUES (8, 2, 12, N'6', N'Güney', 9, CAST(0xA0090000 AS SmallDateTime), CAST(0x47560000 AS SmallDateTime))
SET IDENTITY_INSERT [dbo].[x_seansogrencileri] OFF
/****** Object: Table [dbo].[x_seans] Script Date: 06/01/2012 00:28:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[x_seans](
[x_id] [int] IDENTITY(1,1) NOT NULL,
[x_adi] [nvarchar](50) NULL,
[x_seansturu] [int] NULL,
[x_seansturadi] [nvarchar](50) NULL,
[x_ogrencisayisi] [int] NULL,
CONSTRAINT [PK_x_seans] PRIMARY KEY CLUSTERED
(
[x_id] 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
SET IDENTITY_INSERT [dbo].[x_seans] ON
INSERT [dbo].[x_seans] ([x_id], [x_adi], [x_seansturu], [x_seansturadi], [x_ogrencisayisi]) VALUES (1, N'Class A', 3, N'A', 3)
INSERT [dbo].[x_seans] ([x_id], [x_adi], [x_seansturu], [x_seansturadi], [x_ogrencisayisi]) VALUES (2, N'Class B', 3, N'B', 3)
SET IDENTITY_INSERT [dbo].[x_seans] OFF

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2012-05-31 : 18:27:47

Hi Again.

I have below query finally.

I need WHERE condition instead of (x_seansogrencileri.x_yas = @yas)";. yas means age. if x_seansturu is A, yas should be between +2 and -2 from i set @yas. if x_seansturu is B, yas should be equal @yas like below query. Can you fix it ?
SELECT
x_seansprogrami.x_baslangicsaati as x_baslangicsaati,
x_seansprogrami.x_bitissaati as x_bitissaati,
x_seansprogrami.x_gun as x_gun,
x_seansprogrami.x_seans as x_seans,
x_seansprogrami.x_egitmen as x_egitmen
FROM x_seansprogrami
OUTER APPLY (SELECT TOP 1 x_id, x_adi FROM x_seansegitmenleri WHERE x_seansprogrami.x_egitmen = x_seansegitmenleri.x_adi ) AS x_seansegitmenleri
OUTER APPLY (SELECT TOP 1 x_id, x_adi, x_seansturu, x_seansturadi, x_ogrencisayisi FROM x_seans WHERE x_seansprogrami.x_seans = x_seans.x_adi ) AS x_seans
OUTER APPLY (SELECT TOP 1 x_id, x_yas FROM x_seansogrencileri WHERE x_seans.x_id = x_seansogrencileri.x_seansid ) AS x_seansogrencileri
WHERE (NOT (x_seansprogrami.x_id IS NULL)) AND (x_seansogrencileri.x_yas = @yas)";

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 18:49:11

so you want to compare always first age value for a x_id against passed value of @yas?

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

oguzkaygun
Yak Posting Veteran

53 Posts

Posted - 2012-05-31 : 19:25:04

yes. first age.
Below query is right ?
i think that it works true.
SELECT x_seansprogrami.x_baslangicsaati AS x_baslangicsaati
,x_seansprogrami.x_bitissaati AS x_bitissaati
,x_seansprogrami.x_gun AS x_gun
,x_seansprogrami.x_seans AS x_seans
,x_seansprogrami.x_egitmen AS x_egitmen
FROM x_seansprogrami
OUTER APPLY (
SELECT TOP 1 x_id
,x_adi
FROM x_seansegitmenleri
WHERE x_seansprogrami.x_egitmen = x_seansegitmenleri.x_adi
) AS x_seansegitmenleri
OUTER APPLY (
SELECT TOP 1 x_id
,x_adi
,x_seansturu
,x_seansturadi
,x_ogrencisayisi
FROM x_seans
WHERE x_seansprogrami.x_seans = x_seans.x_adi
) AS x_seans
OUTER APPLY (
SELECT TOP 1 x_id
,x_yas
FROM x_seansogrencileri
WHERE x_seans.x_id = x_seansogrencileri.x_seansid
) AS x_seansogrencileri
WHERE NOT (x_seansprogrami.x_id IS NULL)
AND (
(
x_seansturu = 'B'
AND x_seansogrencileri.x_yas = @yas
)
OR (
x_seansturu = 'A'
AND x_seansogrencileri.x_yas BETWEEN @yas - 2 AND @yas + 2
)
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 19:35:21

the filter based on @yas looks fine. Cant say anything on other subqueries as i dont know what they're for!

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources