1

I have following database structure

Schema

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
  • 2
    GROUP BY domains.id, domains.domain and use MIN(subscriptions.period) in the Select? – ypercubeᵀᴹ Jul 9 '14 at 12:02
  • It is not going to work fine. It will return one record. I will attach jsfiddle in a flash. – Mateusz Nowak Jul 9 '14 at 12:05
  • Have you tried my suggestion? – ypercubeᵀᴹ Jul 9 '14 at 12:21
  • Yes, mon faux pas. It works. You can write below your answer to clarify it to others. – Mateusz Nowak Jul 9 '14 at 12:29
  • Helid - why don't you add an answer with the changes suggested by @ypercube - you can show the exact query that worked for you! – Max Vernon Jul 9 '14 at 13:20
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.