Skip to content
Advertisement

List which has no reports submitted

I have two tables in MySQL database.

  1. tbl_sites (similarly around 3500 sites)
|site_id|site_name|vendor|
|:......|:........|:.....|
|1      |ABC123   |x     |
|2      |ABC124   |y     |
|3      |DSC213   |x     |
|4      |FSA123   |z     |
|5      |FGA123   |z     |
  1. tbl_reports (Similarly multiple reports from each sites)
|report_id|site_id|report_status|
|:........|:......|:............|
|1        |1      |2            |
|2        |2      |2            |
|3        |1      |2            |
|4        |3      |1            |

I want to get the list of sites by site name, where the report count is still zero. A report is considered as successfully submitted when the report_status = 2.

Advertisement

Answer

Using exists logic we can try:

SELECT s.site_name
FROM tbl_sites s
WHERE NOT EXISTS (SELECT 1 FROM tbl_reports r
                  WHERE r.site_id = s.site_id AND r.report_status = 2);
10 People found this is helpful
Advertisement