Solved

Oracle 11 SQL Left Join issue

Posted on 2013-05-30
13
463 Views
Last Modified: 2013-05-31
Hi

I have two tables (see attached spreadsheet for table data); the tables are joined by the wdic_mac column.  The table wdic contains a number of devices that check-in regularly and upload data.  The healthcheck table has a row added each time a device submits a status check to say "I'm here" with the date and time in the lastupdated column.

I want to create a view that shows when a device in the wdic table hasn't checked-in in the past 12 hours.  I tried a left join (wdic to healthcheck) but I get no rows at all :-(

What I'm hoping for is a list of devices from wdic where there isn't a row in the healthcheck table containing a date/time in the past 12 hours.

May someone show me an example with the test data of how I may get the results I'm looking for please.

You may need to tinker with the data in the healthcheck table (delete some rows or adjust the date/time) to make it look like a device hasn't checked-in.

Hope this makes sense and thank you in advance for your help.
healthcheck.xls
0
Comment
Question by:pm620wh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39208209
And what have you coded?
If this is homework, better read: http://www.experts-exchange.com/help/viewHelpPage.jsp?helpPageID=181
0
 

Author Comment

by:pm620wh
ID: 39208269
Hi

Not sure what you mean by homework (if you're asking "am I a student" then the answer is no).  The SQL I was using is as follows:

select WDIC.WDIC_MAC,HEALTHCHECK.DATELASTUPDATED, WDIC.CARID
from wdic left join healthcheck
on wdic.wdic_mac = healthcheck.wdic_mac
where datelastupdated > sysdate -1


I appreciate that "sysdate-1" is not strictly accurate based on my question but I was going to look up how to remove 12 hours from the sysdate later.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39208283
Here:
SELECT wdic.wdic_mac, healthcheck.datelastupdated, wdic.carid
  FROM wdic LEFT JOIN healthcheck ON wdic.wdic_mac = healthcheck.wdic_mac
 WHERE datelastupdated > SYSDATE -0.5;

Open in new window

:p
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:pm620wh
ID: 39208302
Hi

Thanks for the post, unfortunately this still yields an empty result set.  I was hoping to see a list of wdic's from the WDIC table that hadn't inserted a record into the healthcheck table in the past 12 hours but the list is empty :-(

I know my SQL statement isn't correct but I was just showing my attempt which may has misguided you.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39208320
Put a NOT:
SELECT wdic.wdic_mac, healthcheck.datelastupdated, wdic.carid
  FROM wdic LEFT JOIN healthcheck ON wdic.wdic_mac = healthcheck.wdic_mac
 WHERE NOT datelastupdated >= SYSDATE - 0.5
/

Open in new window

;)
0
 
LVL 32

Expert Comment

by:awking00
ID: 39208352
What should the results look like given the sample data you provided?
0
 

Author Comment

by:pm620wh
ID: 39208517
Hi

I've attached an updated spreadsheet with a sample results tab.  Please take a look -hope this helps.
0
 

Author Comment

by:pm620wh
ID: 39208521
Now I've attached it - sorry
healthcheck.xls
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 250 total points
ID: 39208623
ok, try this:
SQL> SELECT wdic.wdic_mac, MAX (healthcheck.datelastupdated) datelastupdated
  2       , wdic.carid
  3    FROM wdic LEFT JOIN healthcheck ON wdic.wdic_mac = healthcheck.wdic_mac
  4   WHERE NOT (SELECT MAX (datelastupdated)
  5                FROM healthcheck h2
  6               WHERE h2.wdic_mac = wdic.wdic_mac) >= SYSDATE - 0.5
  7*  GROUP BY wdic.wdic_mac, wdic.carid
SQL> /

WDIC_MAC                                         DATELASTUPDATED                    CARID
------------------------------------------------ ----------------------------- ----------
247703b0ad98                                     29-May-2013 06:49:31               65507
247703936c98                                     28-May-2013 05:39:18               65505
247703aef4a8                                     29-May-2013 06:22:26               65502
247703b0b164                                     29-May-2013 06:50:54               65510
247703b0bb40                                     29-May-2013 13:50:23               65509
247703b0a6b8                                     29-May-2013 04:13:13               65508
247703b09d28                                     29-May-2013 07:05:55               65503
247703b0bab8                                     07-Mar-2013 05:34:52               65506
247703936cb0                                     25-May-2013 05:36:00               65501
247703b09d54                                     29-May-2013 00:11:31               65504

10 rows selected.

Open in new window

0
 
LVL 32

Accepted Solution

by:
awking00 earned 250 total points
ID: 39208705
SELECT wdic.wdic_mac, max(healthcheck.datelastupdated) datelastupdated, wdic.carid
FROM wdic LEFT JOIN healthcheck ON wdic.wdic_mac = healthcheck.wdic_mac
GROUP BY wdic.wdic_mac, wdic.carid
HAVING max(healthcheck.datelastupdated) < SYSDATE -0.5;
0
 

Author Closing Comment

by:pm620wh
ID: 39208825
Both the above solutions work perfectly - thank you to both of you for your rapid replies.  I've learnt from this and fixed my issue.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39209772
forgive me, but I think there is no point in that left join in the accepted answer, the results could be misleading as it won't report "missing" healthchecks - I believe you need to test for null in addition to the time.

http://sqlfiddle.com/#!4/9c4f3/1 {edit, correction to url, sorry}

SELECT
  wdic.wdic_mac
, wdic.carid
, max(healthcheck.datelastupdated) datelastupdated
FROM wdic 
LEFT JOIN healthcheck ON wdic.wdic_mac = healthcheck.wdic_mac
GROUP BY
  wdic.wdic_mac
, wdic.carid
HAVING max(healthcheck.datelastupdated) < SYSDATE -0.5
or max(healthcheck.datelastupdated) is null;

Open in new window

0
 

Author Comment

by:pm620wh
ID: 39210333
Hi

Thank you so much for the additional feedback and enhanced logic.  I'm so sorry I've closed the question and can't give you any points.

Really appreciate your help.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

688 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