mysql left join, how to write such query?

Question

I have

first:

select url from urls where site = '$value' order by url

second:

select id, widget_name from widgets where display_urls LIKE 'urls.url' and is_father=1 order by id

Third:

select id, widget_name from widgets where display_urls RLIKE 'urls.url' and is_father=0 order by id

Fourth:

select id, widget_name, father_widget from widgets where father_widget = 'widgets.id' order by id

The fourth query must only take widgets.id from second query the second and third queries take the value of urls.url from the first query

how to write all those in one mysql query using left join ?

Thanks in advance for your answers :)

Answer 1

SELECT  w.id, w.widget_name, c.*
FROM    urls
JOIN    widgets w
ON      w.display_urls LIKE urls.url
        OR w.display_urls RLIKE urls.url
LEFT JOIN
        widgets с
ON      c.father_widget = w.id
        AND w.display_urls LIKE urls.url
        AND w.display_urls NOT RLIKE urls.url
WHERE   site = $value
ORDER BY
        url

This will select children only for those widgets that are LIKE but nor RLIKE the corresponding url.