MySQL help do I use Where not exist or Join? - Laravel I have two tables as follows:

1) urls

    id    domain             created_at
------------------------------------------------
    1     google.com           2016-11-30 00:00:00
    2     test.com             2016-11-29 00:00:00
    3     example.com      2016-11-26 00:00:00


etc..
2) links

     id  urls_id   end_date              created_at            status
---------------------------------------------------------------------------
     1     2       2016-11-30 00:00:00   2016-11-30 00:00:00     Approved
     2     2       2016-12-01 00:00:00   2016-11-30 00:00:00     Approved
     3     2       NULL                  2016-11-30 00:00:00     Approved
     4     2       2017-01-01 00:00:00   2016-12-01 00:00:00     Approved
     5     2       2016-01-01 00:00:00   2016-12-01 00:00:00     Pending
     6     3       2016-11-24 00:00:00   2016-12-01 00:00:00     Pending
     7     3       2016-01-01 00:00:00   2016-12-01 00:00:00     Approved
     8     3       NUll                  2016-12-02 00:00:00     Pending
     9     3       2016-01-08 00:00:00   2016-12-01 00:00:00     Approved
     10    3       NUll                  2016-12-02 00:00:00     Pending


I would like to write a MySQL Query to return with these conditions:
one row per domain that follows these conditions:

1). end_date NOT NULL  AND
2). end_date &gt; Carbon::now()   (I am using carbon) not expired AND
3). status is "Approved" AND
4). return latest end_date closer to present or now.


So, for example, it will look and find all "urls_id"= 2 and select ALL status "Approved" and look for end_date that is NOT NULL and not expired, but since there is one NULL then it won't return it, then move on to "urls_id"= 3 and we have 2 approved and both expired and there is no end_date NULL or not expired so we only will return that row of the latest end_date.

Here is an output

  id     domain          end_date_max        
  ------------------------------
  9      example.com     2016-01-08 00:00:00 


Here is what I have tried but it's selecting all domains (using Where not exists)

select * from urls
where not exists (
    select * 
    from links 
    where urls.id = links.urls_id 
    and (links.status = "Approved" 
        and links.end_date is not null) 
    and (links.status = "Approved" 
        and links.end_date &gt; 2016-12-08 23:46:16)
) 
group by urls.domain


How we can do this in one SQL query? In Laravel or just plain Raw query.

Thanks
SELECT 
    u1.id,
    u1.domain,
    t1.end_date

FROM urls u1
INNER JOIN 
        (SELECT 
            l1.urls_id,
            MIN(end_date) as end_date       
         FROM urls u2 
         INNER JOIN links l1 
             ON u2.id = l1.urls_id 
            AND l1.status = "Approved" 
            AND (l1.end_date &gt; now() OR l1.end_date IS NOT NULL)
         GROUP BY l1.urls_id) t1
ON u1.id = t1.urls_id


Source requirements (not 100% sure about p4, so this could be changed): 

1). end_date NOT NULL  AND
2). end_date &gt; now()   
3). status is "Approved" AND
4). return latest end_date closer to present or now.