Bird757
asked on
Basic Question on joining 2 tables with an outer join
Please could someone help me get this join right. I Need All the rows of the list, and the matching rows of the Beacons - but for all my trying I only get matching rows... It is probably so simple but I can't see it.
Select B.[iKey] AS iKeyID, B.[iSeq], L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa ble_Hour]( NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
JOIN [iTrail].[dbo].[BeaconUnit Hit] B ON B.[dTo] BETWEEN L.[dFrom] And L.[dTo]
Select B.[iKey] AS iKeyID, B.[iSeq], L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa
JOIN [iTrail].[dbo].[BeaconUnit
ASKER
Hi Sharath
That is my problem - a LEFT JOIN is not working. I have the code below working (using OUTER APPLY) but it feels like there should be an easier way. I don't understand why LEFT JOIN does not work, and I also don't understand how the operation of OUTER APPLY allows it to work.
LEFT JOIN - Not Working:
Select B.[iKey] AS iKeyID, L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa ble_Hour]( NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
LEFT JOIN [iTrail].[dbo].[BeaconUnit Hit] B ON B.[dTo] BETWEEN L.[dFrom] And L.[dTo]
Where B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
Results is:
iKeyID cDisplay
9153 16
9157 17
9164 18
9180 20
9181 20
9189 23
9199 01
9207 03
I have set it up with an OUTER APPLY (and that works) but that contradicts what I know about Left and Right joins. I hope someone can cast light on what I am doing wrong?
OUTER APPLY - Returns the results I want but I don't understand why this works, and LEFT does not:
Select
P0.iKeyID, L.[cDisplay]
From
[iData].[dbo].[func_ListTa ble_Hour]( NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
OUTER APPLY
(
Select
B.[iKey] AS iKeyID
From
[iTrail].[dbo].[BeaconUnit Hit] B
Where
B.[dTo] BETWEEN L.[dFrom] And L.[dTo]
And
B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
) AS P0
Result is:
iKeyID cDisplay
9153 16
9157 17
9164 18
NULL 19
9180 20
9181 20
NULL 21
NULL 22
9189 23
NULL 00
9199 01
NULL 02
9207 03
NULL 04
NULL 05
NULL 06
That is my problem - a LEFT JOIN is not working. I have the code below working (using OUTER APPLY) but it feels like there should be an easier way. I don't understand why LEFT JOIN does not work, and I also don't understand how the operation of OUTER APPLY allows it to work.
LEFT JOIN - Not Working:
Select B.[iKey] AS iKeyID, L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa
LEFT JOIN [iTrail].[dbo].[BeaconUnit
Where B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
Results is:
iKeyID cDisplay
9153 16
9157 17
9164 18
9180 20
9181 20
9189 23
9199 01
9207 03
I have set it up with an OUTER APPLY (and that works) but that contradicts what I know about Left and Right joins. I hope someone can cast light on what I am doing wrong?
OUTER APPLY - Returns the results I want but I don't understand why this works, and LEFT does not:
Select
P0.iKeyID, L.[cDisplay]
From
[iData].[dbo].[func_ListTa
OUTER APPLY
(
Select
B.[iKey] AS iKeyID
From
[iTrail].[dbo].[BeaconUnit
Where
B.[dTo] BETWEEN L.[dFrom] And L.[dTo]
And
B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
) AS P0
Result is:
iKeyID cDisplay
9153 16
9157 17
9164 18
NULL 19
9180 20
9181 20
NULL 21
NULL 22
9189 23
NULL 00
9199 01
NULL 02
9207 03
NULL 04
NULL 05
NULL 06
You said LEFT JOIN is not working. Do you want more/less records from the result set.
Results is:
iKeyID cDisplay
9153 16
9157 17
9164 18
9180 20
9181 20
9189 23
9199 01
9207 03
Can you provide some sample data from two tables with expected result?
Results is:
iKeyID cDisplay
9153 16
9157 17
9164 18
9180 20
9181 20
9189 23
9199 01
9207 03
Can you provide some sample data from two tables with expected result?
ASKER
What I need is to list the hours in a Work Schedule (16h00 to 06h00), and to then "mark" the hours where there was no activity (a BeaconHit corresponds to activity). So I created a function to return the Hours of Interest:
Select * From [iData].[dbo].[func_ListTa ble_Hour]( NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F')
iKey cDisplay dFrom dTo
1 16 2011-04-22 16:00:00.000 2011-04-22 17:00:00.000
2 17 2011-04-22 17:00:00.000 2011-04-22 18:00:00.000
3 18 2011-04-22 18:00:00.000 2011-04-22 19:00:00.000
4 19 2011-04-22 19:00:00.000 2011-04-22 20:00:00.000
5 20 2011-04-22 20:00:00.000 2011-04-22 21:00:00.000
6 21 2011-04-22 21:00:00.000 2011-04-22 22:00:00.000
7 22 2011-04-22 22:00:00.000 2011-04-22 23:00:00.000
8 23 2011-04-22 23:00:00.000 2011-04-23 00:00:00.000
9 00 2011-04-23 00:00:00.000 2011-04-23 01:00:00.000
10 01 2011-04-23 01:00:00.000 2011-04-23 02:00:00.000
11 02 2011-04-23 02:00:00.000 2011-04-23 03:00:00.000
12 03 2011-04-23 03:00:00.000 2011-04-23 04:00:00.000
13 04 2011-04-23 04:00:00.000 2011-04-23 05:00:00.000
14 05 2011-04-23 05:00:00.000 2011-04-23 06:00:00.000
15 06 2011-04-23 06:00:00.000 2011-04-23 07:00:00.000
The BeaconHits depends on various restrictions, which are not relevant to the query but are included because otherwise there are thousands of rows returned:
Select iKey, iBeaconKey, dTo From [iTrail].[dbo].[BeaconUnit Hit] B Where B.[dTo] BETWEEN '22 Apr 2011 16:00' And '23 Apr 2011 06:00'
And B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
iKey iBeaconKey dTo
9153 2 2011-04-22 16:26:55.000
9180 2 2011-04-22 20:40:00.000
9157 4 2011-04-22 17:10:00.000
9164 4 2011-04-22 18:20:00.000
9181 4 2011-04-22 20:40:00.000
9189 4 2011-04-22 23:10:00.000
9199 4 2011-04-23 01:20:00.000
9207 4 2011-04-23 03:20:00.000
The result I want is:
Everything from the List (i.e. 16h00 / 17h00 / 18h00 / 19h00 / ... / 06h00)
And then I want (16h00 Y / 17h00 Y / 19h00 N / 20h00 Y / 21h00 N / ... etc)
It really should be as simple as a Left Join, but it appears that because the Time Range on the Beacons Table is being controlled from the Base List, that List is is being restricted to matching records. It does not make sense to me; I really don't understand why the LEFT JOIN is not working, but it is not.
So the result I am trying to achieve is:
iKeyID cDisplay
9153 16
9157 17
9164 18
NULL 19
9180 20
9181 20
NULL 21
NULL 22
.... etc
Which I'll manipulate into a Pivot using COUNT(iKeyID):
Count cDisplay
1 16
1 17
1 18
0 19
2 20
0 21
0 22
Select * From [iData].[dbo].[func_ListTa
iKey cDisplay dFrom dTo
1 16 2011-04-22 16:00:00.000 2011-04-22 17:00:00.000
2 17 2011-04-22 17:00:00.000 2011-04-22 18:00:00.000
3 18 2011-04-22 18:00:00.000 2011-04-22 19:00:00.000
4 19 2011-04-22 19:00:00.000 2011-04-22 20:00:00.000
5 20 2011-04-22 20:00:00.000 2011-04-22 21:00:00.000
6 21 2011-04-22 21:00:00.000 2011-04-22 22:00:00.000
7 22 2011-04-22 22:00:00.000 2011-04-22 23:00:00.000
8 23 2011-04-22 23:00:00.000 2011-04-23 00:00:00.000
9 00 2011-04-23 00:00:00.000 2011-04-23 01:00:00.000
10 01 2011-04-23 01:00:00.000 2011-04-23 02:00:00.000
11 02 2011-04-23 02:00:00.000 2011-04-23 03:00:00.000
12 03 2011-04-23 03:00:00.000 2011-04-23 04:00:00.000
13 04 2011-04-23 04:00:00.000 2011-04-23 05:00:00.000
14 05 2011-04-23 05:00:00.000 2011-04-23 06:00:00.000
15 06 2011-04-23 06:00:00.000 2011-04-23 07:00:00.000
The BeaconHits depends on various restrictions, which are not relevant to the query but are included because otherwise there are thousands of rows returned:
Select iKey, iBeaconKey, dTo From [iTrail].[dbo].[BeaconUnit
And B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
iKey iBeaconKey dTo
9153 2 2011-04-22 16:26:55.000
9180 2 2011-04-22 20:40:00.000
9157 4 2011-04-22 17:10:00.000
9164 4 2011-04-22 18:20:00.000
9181 4 2011-04-22 20:40:00.000
9189 4 2011-04-22 23:10:00.000
9199 4 2011-04-23 01:20:00.000
9207 4 2011-04-23 03:20:00.000
The result I want is:
Everything from the List (i.e. 16h00 / 17h00 / 18h00 / 19h00 / ... / 06h00)
And then I want (16h00 Y / 17h00 Y / 19h00 N / 20h00 Y / 21h00 N / ... etc)
It really should be as simple as a Left Join, but it appears that because the Time Range on the Beacons Table is being controlled from the Base List, that List is is being restricted to matching records. It does not make sense to me; I really don't understand why the LEFT JOIN is not working, but it is not.
So the result I am trying to achieve is:
iKeyID cDisplay
9153 16
9157 17
9164 18
NULL 19
9180 20
9181 20
NULL 21
NULL 22
.... etc
Which I'll manipulate into a Pivot using COUNT(iKeyID):
Count cDisplay
1 16
1 17
1 18
0 19
2 20
0 21
0 22
For your case Left Outer Join is good.
If your demand is opposite to this then Right Outer Join is the best.
If your demand is opposite to this then Right Outer Join is the best.
ASKER
My problem is an OUTER JOIN "should" work, but it is not working.
I have tried the combinations below and all return exactly the same result set. The only way I can get the full List, and the sub-set of Beacons is using an OUTER APPLY - and I cannot work out why.
All these queries return exactly the same results:
Select B.[iKey] AS iKeyID, L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa ble_Hour]( NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
JOIN [iTrail].[dbo].[BeaconUnit Hit] B ON B.[dTo] BETWEEN L.[dFrom] And L.[dTo]
Where B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
Select B.[iKey] AS iKeyID, L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa ble_Hour]( NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
LEFT OUTER JOIN [iTrail].[dbo].[BeaconUnit Hit] B ON B.[dTo] BETWEEN L.[dFrom] And L.[dTo]
Where B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
Select B.[iKey] AS iKeyID, L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa ble_Hour]( NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
RIGHT OUTER JOIN [iTrail].[dbo].[BeaconUnit Hit] B ON B.[dTo] BETWEEN L.[dFrom] And L.[dTo]
Where B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
** I even tried switching the order of the tables around (which should make no difference - and does not)
Select B.[iKey] AS iKeyID, L.[cDisplay], L.[dFrom]
From
[iTrail].[dbo].[BeaconUnit Hit] B
LEFT OUTER JOIN [iData].[dbo].[func_ListTa ble_Hour]( NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L ON B.[dTo] BETWEEN L.[dFrom] And L.[dTo]
Where B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
Select B.[iKey] AS iKeyID, L.[cDisplay], L.[dFrom]
From
[iTrail].[dbo].[BeaconUnit Hit] B
RIGHT OUTER JOIN [iData].[dbo].[func_ListTa ble_Hour]( NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L ON B.[dTo] BETWEEN L.[dFrom] And L.[dTo]
Where B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
iKeyID cDisplay dFrom
----------- ----------- -----------------------
9153 16 2011-04-22 16:00:00.000
9157 17 2011-04-22 17:00:00.000
9164 18 2011-04-22 18:00:00.000
9180 20 2011-04-22 20:00:00.000
9181 20 2011-04-22 20:00:00.000
9189 23 2011-04-22 23:00:00.000
9199 01 2011-04-23 01:00:00.000
9207 03 2011-04-23 03:00:00.000
(8 row(s) affected)
iKeyID cDisplay dFrom
----------- ----------- -----------------------
9153 16 2011-04-22 16:00:00.000
9157 17 2011-04-22 17:00:00.000
9164 18 2011-04-22 18:00:00.000
9180 20 2011-04-22 20:00:00.000
9181 20 2011-04-22 20:00:00.000
9189 23 2011-04-22 23:00:00.000
9199 01 2011-04-23 01:00:00.000
9207 03 2011-04-23 03:00:00.000
(8 row(s) affected)
iKeyID cDisplay dFrom
----------- ----------- -----------------------
9153 16 2011-04-22 16:00:00.000
9180 20 2011-04-22 20:00:00.000
9157 17 2011-04-22 17:00:00.000
9164 18 2011-04-22 18:00:00.000
9181 20 2011-04-22 20:00:00.000
9189 23 2011-04-22 23:00:00.000
9199 01 2011-04-23 01:00:00.000
9207 03 2011-04-23 03:00:00.000
(8 row(s) affected)
iKeyID cDisplay dFrom
----------- ----------- -----------------------
9153 16 2011-04-22 16:00:00.000
9180 20 2011-04-22 20:00:00.000
9157 17 2011-04-22 17:00:00.000
9164 18 2011-04-22 18:00:00.000
9181 20 2011-04-22 20:00:00.000
9189 23 2011-04-22 23:00:00.000
9199 01 2011-04-23 01:00:00.000
9207 03 2011-04-23 03:00:00.000
(8 row(s) affected)
iKeyID cDisplay dFrom
----------- ----------- -----------------------
9153 16 2011-04-22 16:00:00.000
9157 17 2011-04-22 17:00:00.000
9164 18 2011-04-22 18:00:00.000
9180 20 2011-04-22 20:00:00.000
9181 20 2011-04-22 20:00:00.000
9189 23 2011-04-22 23:00:00.000
9199 01 2011-04-23 01:00:00.000
9207 03 2011-04-23 03:00:00.000
(8 row(s) affected)
The only way I can get the full list is with this query:
Select
P0.iKeyID, L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa ble_Hour]( NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
OUTER APPLY
(
Select
B.[iKey] AS iKeyID
From
[iTrail].[dbo].[BeaconUnit Hit] B
Where
B.[dTo] BETWEEN L.[dFrom] And L.[dTo]
And
B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
) AS P0
iKeyID cDisplay dFrom
----------- ----------- -----------------------
9153 16 2011-04-22 16:00:00.000
9157 17 2011-04-22 17:00:00.000
9164 18 2011-04-22 18:00:00.000
NULL 19 2011-04-22 19:00:00.000
9180 20 2011-04-22 20:00:00.000
9181 20 2011-04-22 20:00:00.000
NULL 21 2011-04-22 21:00:00.000
NULL 22 2011-04-22 22:00:00.000
9189 23 2011-04-22 23:00:00.000
NULL 00 2011-04-23 00:00:00.000
9199 01 2011-04-23 01:00:00.000
NULL 02 2011-04-23 02:00:00.000
9207 03 2011-04-23 03:00:00.000
NULL 04 2011-04-23 04:00:00.000
NULL 05 2011-04-23 05:00:00.000
NULL 06 2011-04-23 06:00:00.000
(16 row(s) affected)
I have tried the combinations below and all return exactly the same result set. The only way I can get the full List, and the sub-set of Beacons is using an OUTER APPLY - and I cannot work out why.
All these queries return exactly the same results:
Select B.[iKey] AS iKeyID, L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa
JOIN [iTrail].[dbo].[BeaconUnit
Where B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
Select B.[iKey] AS iKeyID, L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa
LEFT OUTER JOIN [iTrail].[dbo].[BeaconUnit
Where B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
Select B.[iKey] AS iKeyID, L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa
RIGHT OUTER JOIN [iTrail].[dbo].[BeaconUnit
Where B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
** I even tried switching the order of the tables around (which should make no difference - and does not)
Select B.[iKey] AS iKeyID, L.[cDisplay], L.[dFrom]
From
[iTrail].[dbo].[BeaconUnit
LEFT OUTER JOIN [iData].[dbo].[func_ListTa
Where B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
Select B.[iKey] AS iKeyID, L.[cDisplay], L.[dFrom]
From
[iTrail].[dbo].[BeaconUnit
RIGHT OUTER JOIN [iData].[dbo].[func_ListTa
Where B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
iKeyID cDisplay dFrom
----------- ----------- -----------------------
9153 16 2011-04-22 16:00:00.000
9157 17 2011-04-22 17:00:00.000
9164 18 2011-04-22 18:00:00.000
9180 20 2011-04-22 20:00:00.000
9181 20 2011-04-22 20:00:00.000
9189 23 2011-04-22 23:00:00.000
9199 01 2011-04-23 01:00:00.000
9207 03 2011-04-23 03:00:00.000
(8 row(s) affected)
iKeyID cDisplay dFrom
----------- ----------- -----------------------
9153 16 2011-04-22 16:00:00.000
9157 17 2011-04-22 17:00:00.000
9164 18 2011-04-22 18:00:00.000
9180 20 2011-04-22 20:00:00.000
9181 20 2011-04-22 20:00:00.000
9189 23 2011-04-22 23:00:00.000
9199 01 2011-04-23 01:00:00.000
9207 03 2011-04-23 03:00:00.000
(8 row(s) affected)
iKeyID cDisplay dFrom
----------- ----------- -----------------------
9153 16 2011-04-22 16:00:00.000
9180 20 2011-04-22 20:00:00.000
9157 17 2011-04-22 17:00:00.000
9164 18 2011-04-22 18:00:00.000
9181 20 2011-04-22 20:00:00.000
9189 23 2011-04-22 23:00:00.000
9199 01 2011-04-23 01:00:00.000
9207 03 2011-04-23 03:00:00.000
(8 row(s) affected)
iKeyID cDisplay dFrom
----------- ----------- -----------------------
9153 16 2011-04-22 16:00:00.000
9180 20 2011-04-22 20:00:00.000
9157 17 2011-04-22 17:00:00.000
9164 18 2011-04-22 18:00:00.000
9181 20 2011-04-22 20:00:00.000
9189 23 2011-04-22 23:00:00.000
9199 01 2011-04-23 01:00:00.000
9207 03 2011-04-23 03:00:00.000
(8 row(s) affected)
iKeyID cDisplay dFrom
----------- ----------- -----------------------
9153 16 2011-04-22 16:00:00.000
9157 17 2011-04-22 17:00:00.000
9164 18 2011-04-22 18:00:00.000
9180 20 2011-04-22 20:00:00.000
9181 20 2011-04-22 20:00:00.000
9189 23 2011-04-22 23:00:00.000
9199 01 2011-04-23 01:00:00.000
9207 03 2011-04-23 03:00:00.000
(8 row(s) affected)
The only way I can get the full list is with this query:
Select
P0.iKeyID, L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa
OUTER APPLY
(
Select
B.[iKey] AS iKeyID
From
[iTrail].[dbo].[BeaconUnit
Where
B.[dTo] BETWEEN L.[dFrom] And L.[dTo]
And
B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
) AS P0
iKeyID cDisplay dFrom
----------- ----------- -----------------------
9153 16 2011-04-22 16:00:00.000
9157 17 2011-04-22 17:00:00.000
9164 18 2011-04-22 18:00:00.000
NULL 19 2011-04-22 19:00:00.000
9180 20 2011-04-22 20:00:00.000
9181 20 2011-04-22 20:00:00.000
NULL 21 2011-04-22 21:00:00.000
NULL 22 2011-04-22 22:00:00.000
9189 23 2011-04-22 23:00:00.000
NULL 00 2011-04-23 00:00:00.000
9199 01 2011-04-23 01:00:00.000
NULL 02 2011-04-23 02:00:00.000
9207 03 2011-04-23 03:00:00.000
NULL 04 2011-04-23 04:00:00.000
NULL 05 2011-04-23 05:00:00.000
NULL 06 2011-04-23 06:00:00.000
(16 row(s) affected)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is interesting - and explains my long-term problem with OUTER joins. The problem is in my use of "WHERE" within the same query as the JOIN.
I see that this works:
Select B.[iKey] AS iKeyID, L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa ble_Hour]( NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
LEFT OUTER JOIN [iTrail].[dbo].[BeaconUnit Hit] B ON B.[dTo] BETWEEN L.[dFrom] And L.[dTo]
And B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
BUT This one does not:
Select B.[iKey] AS iKeyID, L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa ble_Hour]( NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
LEFT OUTER JOIN [iTrail].[dbo].[BeaconUnit Hit] B ON B.[dTo] BETWEEN L.[dFrom] And L.[dTo]
WHERE B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
Subtle. But yes, that is why I have always had a problem getting Outer Joins to work. Thank you.
I see that this works:
Select B.[iKey] AS iKeyID, L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa
LEFT OUTER JOIN [iTrail].[dbo].[BeaconUnit
And B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
BUT This one does not:
Select B.[iKey] AS iKeyID, L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa
LEFT OUTER JOIN [iTrail].[dbo].[BeaconUnit
WHERE B.[iEventKey] = 1 And B.[iEventRouteKey] = 111 And B.[iBeaconKey] IN (1, 2, 3, 4)
Subtle. But yes, that is why I have always had a problem getting Outer Joins to work. Thank you.
Select B.[iKey] AS iKeyID, B.[iSeq], L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTa
LEFT JOIN [iTrail].[dbo].[BeaconUnit