Netezza SQL to join a table if that table contains relevant data

Question

I am trying to join tables based on certain columns being within a specified time range. I've tried several methods and haven't been able to solve this.

I start with a simple query:

SELECT *
FROM admin.mktdmtunit c
WHERE c.unitid IN ('2756')
AND c.calledofftime between to_timestamp('2012-12-05 05:00:01','yyyy-mm-dd hh:mi:ss') 
    AND to_timestamp('2012-12-06 05:00:00','yyyy-mm-dd hh:mi:ss')

that returns 1 row.

I then add another table:

 SELECT *
    FROM admin.mktdmtunit c
    INNER JOIN admin.mktmanualdispatch_all mrd 
    ON c.unitid = mrd.unitid
    where c.unitid in ('2756')
    AND (c.calledofftime between to_timestamp('2012-12-05 05:00:01','yyyy-mm-dd hh:mi:ss') 
        and to_timestamp('2012-12-06 05:00:00','yyyy-mm-dd hh:mi:ss')
        OR mrd.effectivetime  between to_timestamp('2012-12-05 05:00:01','yyyy-mm-dd           hh:mi:ss') 
            and to_timestamp('2012-12-06 05:00:00','yyyy-mm-dd hh:mi:ss'))

that returns 4 lines based on 4 entries in the mrd table for that unitid. But none of these are relevant to that date so I'd like to not see that information.

So I tried this:

SELECT *
FROM admin.mktdmtunit c
where c.unitid in ('2756')
AND c.calledofftime between to_timestamp('2012-12-05 05:00:01','yyyy-mm-dd hh:mi:ss') 
    and to_timestamp('2012-12-06 05:00:00','yyyy-mm-dd hh:mi:ss')
OR (exists(SELECT mrd.effectivetime 
    FROM admin.mktmanualdispatch_all mrd 
    WHERE mrd.effectivetime  between to_timestamp('2012-12-05 05:00:01','yyyy-mm-dd hh:mi:ss') 
        and to_timestamp('2012-12-06 05:00:00','yyyy-mm-dd hh:mi:ss')
    AND c.unitid = mrd.unitid)

but this gives an error saying that it expects a "," before the "mrd.unitid" in the last line.

So I also tried this:

SELECT *
FROM admin.mktdmtunit c
INNER JOIN admin.mktmanualdispatch_all mrd 
    ON
    mrd.effectivetime  between to_timestamp('2012-12-05 05:00:01','yyyy-mm-dd hh:mi:ss') 
        and to_timestamp('2012-12-06 05:00:00','yyyy-mm-dd hh:mi:ss')
    AND c.unitid = mrd.unitid
where c.unitid in ('2756')
AND c.calledofftime between to_timestamp('2012-12-05 05:00:01','yyyy-mm-dd hh:mi:ss') 
    and to_timestamp('2012-12-06 05:00:00','yyyy-mm-dd hh:mi:ss')

but this doesn't return any lines.

So to be clear, what I am wanting from this specific query is 1 row that has the information based on c.calledofftime and no information pulled in from the other table since there is no relevant data based on mrd.effective time. Thanks

Answer 1

It has already been answered by the poster, but yeah, left join the second table and include the mrd.effectivetime criteria in the join logic so you'll receive NULLs if none exist. This will allow you to maintain your core information from the primary table.