• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

Oracle 11 SQL Left Join issue

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
pm620wh
Asked:
pm620wh
  • 6
  • 4
  • 2
  • +1
2 Solutions
 
MikeOM_DBACommented:
And what have you coded?
If this is homework, better read: http://www.experts-exchange.com/help/viewHelpPage.jsp?helpPageID=181
0
 
pm620whAuthor Commented:
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
 
MikeOM_DBACommented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
pm620whAuthor Commented:
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
 
MikeOM_DBACommented:
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
 
awking00Commented:
What should the results look like given the sample data you provided?
0
 
pm620whAuthor Commented:
Hi

I've attached an updated spreadsheet with a sample results tab.  Please take a look -hope this helps.
0
 
pm620whAuthor Commented:
Now I've attached it - sorry
healthcheck.xls
0
 
MikeOM_DBACommented:
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
 
awking00Commented:
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
 
pm620whAuthor Commented:
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
 
PortletPaulCommented:
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
 
pm620whAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now