How to make MySQL join one-to-many with lowest value? I have following database structure



Domain has many URL which has many positions which belongs to phrase which belongs to user which belongs to subscription.

In subscriptions table I have period field and I need to fetch all domains with lowest period value from subscriptions. To accomplish this challenge I've created following query

 select
        domains.domain,
        domains.id,
        subscriptions.period

        from
            domains
        inner join urls ON urls.domain_id = domains.id
        inner join positions ON positions.url_id = urls.id
        inner join phrases ON phrases.id = positions.phrase_id
        inner join users ON users.id = phrases.user_id
        inner join subscriptions ON subscriptions.id = users.subscription_id


Which return me results similar to

"domain": "http://www.example.com",
"id":     60,
"period": 7

"domain": "http://www.example.com",
"id":     60,
"period": 30

"domain": "http://www.other-example.com",
"id":     61,
"period": 7


In the results one of the domains occurs more than on time, because two different user with varied subscription have the same domain. 

How can I filter results and get only lowest period value for given domain?

"domain": "http://www.example.com",
"id":     60,
"period": 7

"domain": "http://www.other-example.com",
"id":     61,
"period": 7
As suggested @ypercube it can be done by grouping by domains.id, domains.domain and selecting min(subscriptions.period). Complete query:

 select
        domains.domain,
        domains.id,
        MIN(subscriptions.period) as period

        from
            domains
        inner join urls ON urls.domain_id = domains.id
        inner join positions ON positions.url_id = urls.id
        inner join phrases ON phrases.id = positions.phrase_id
        inner join users ON users.id = phrases.user_id
        inner join subscriptions ON subscriptions.id = users.subscription_id

        GROUP BY domains.id


SQL Fiddle: http://www.sqlfiddle.com/#!2/438c1/2