[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Basic Question on joining 2 tables with an outer join

Posted on 2011-05-06
8
Medium Priority
?
241 Views
Last Modified: 2012-05-11
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]
0
Comment
Question by:Bird757
  • 4
  • 3
8 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35707682
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]
0
 

Author Comment

by:Bird757
ID: 35710995
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
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35711002
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?
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:Bird757
ID: 35711076
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
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35711214
For your case Left Outer Join is good.
If your demand is opposite to this then Right Outer Join is the best.
0
 

Author Comment

by:Bird757
ID: 35711279
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)
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35711568
try this.
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)

Open in new window

0
 

Author Closing Comment

by:Bird757
ID: 35711656
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.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question