Link to home
Start Free TrialLog in
Avatar of Bird757
Bird757Flag for South Africa

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_ListTable_Hour](NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
JOIN [iTrail].[dbo].[BeaconUnitHit] B ON B.[dTo] BETWEEN L.[dFrom] And L.[dTo]
Avatar of Sharath S
Sharath S
Flag of United States of America image

Try LEFT JOIN.

Select B.[iKey] AS iKeyID, B.[iSeq], L.[cDisplay], L.[dFrom]
From
[iData].[dbo].[func_ListTable_Hour](NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
LEFT JOIN [iTrail].[dbo].[BeaconUnitHit] B ON B.[dTo] BETWEEN L.[dFrom] And L.[dTo]
Avatar of Bird757

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_ListTable_Hour](NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
      LEFT JOIN [iTrail].[dbo].[BeaconUnitHit] 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_ListTable_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].[BeaconUnitHit] 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
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?
Avatar of Bird757

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_ListTable_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].[BeaconUnitHit] 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
For your case Left Outer Join is good.
If your demand is opposite to this then Right Outer Join is the best.
Avatar of Bird757

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_ListTable_Hour](NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
      JOIN [iTrail].[dbo].[BeaconUnitHit] 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_ListTable_Hour](NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
      LEFT OUTER JOIN [iTrail].[dbo].[BeaconUnitHit] 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_ListTable_Hour](NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
      RIGHT OUTER JOIN [iTrail].[dbo].[BeaconUnitHit] 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].[BeaconUnitHit] B
      LEFT OUTER JOIN [iData].[dbo].[func_ListTable_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].[BeaconUnitHit] B
      RIGHT OUTER JOIN [iData].[dbo].[func_ListTable_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_ListTable_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].[BeaconUnitHit] 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)
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Bird757

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_ListTable_Hour](NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
      LEFT OUTER JOIN [iTrail].[dbo].[BeaconUnitHit] 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_ListTable_Hour](NULL, 'hh', 1, '22 Apr 2011 16:00', '23 Apr 2011 06:00', 'F') L
      LEFT OUTER JOIN [iTrail].[dbo].[BeaconUnitHit] 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.