Mysql Query two tables

Question

I have 2 tables route and combo.

I have tried many queries (union and joins) with no success.

I would like to get results where no Route entry exists in the combo table for each rt_num in the route table where a PDate has entries. I am totally lost at this point.

Route Table

 id |    rt_num    | name |
----|--------------|------|
  1 | 123 east     | Ron  |
  2 | 2North       | Ted  |
  4 | 909          | Ted  |

Combo Table

 id | Route    | PDate               |
----|----------|---------------------|
  1 | 123 east | 2017-02-15 08:55:04 |
  2 | 2North   | 2017-02-16 10:55:04 |

Results Table

  rt_num  |    Pdate    |
----------|-------------|
123 east  | 2017-02-16  |
123 east  | 2017-02-17  |
2North    | 2017-02-15  |
2Notrh    | 2017-02-17  |
909       | 2017-02-15  |
909       | 2017-02-16  |
909       | 2017-02-17  |

More Info:

I have these 2 queries: SELECT DISTINCT r.rt_num , DATE_FORMAT(PDate,'%Y-%m-%d') from route r, combo; SELECT DISTINCT Route , DATE_FORMAT(PDate,'%Y-%m-%d') from combo; each return the data needed, I need to display them side by side matching rt_num with PDate to Route with PDate or NULL I hope this is a better explanation of what I am trying to do.

Answer 1

The following Query does what I need it to do thanks to the help by Peter Brawley

select a.rt_num, a.tdate, b.Route
from ( 
  select distinct r.rt_num, date_format(t.PDdate,'%y-%m-%d') as tdate
  from route r 
  join combo t on r.rt_num IS NOT NULL    
  ) as a
left join (
  select distinct Route, date_format(PDdate,'%y-%m-%d') as pudate
  from combo
) as b on b.Route = a.rt_num and b.pudate = a.tdate ; 

Answer 2

You can use NOT IN QUERY

SELECT * FROM `route` WHERE route.rt_num NOT IN (SELECT `Route ` FROM `combo`);

This query return value which is not available in combo table.