How to Make MySQL Join One-to-Many with Lowest Value

MySQL

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

Best Answer

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

Related Solutions

Mysql – thesql fine tuning: open_tables, opened_tables

Three points:

I don't see the immediate connection between your "too many connections" and the table cache. There might be such connection, but you have not provided info which suggests the two relate.
I disagree with the advice about "If Open_tables climbs above (Opened_tables / Uptime), I have a cause for concern.."
You haven't listed the table_open_cache & table_definition_cache settings. The table_cache variable is an old one.

Some suggestions:

With regard "too many connections", try the following: Set max_connections to some reasonable value, like 300, throwing around a number. Set open_files_limit to a very high values, like 8192. By default you are only allowed 1024 open files on linux. Sockets, threads, table descriptors -- all map to files. Do you have some connection pool which holds many open (idle) connections, with some keepalive? How many processes do you get on a typical SHOW PROCESSLIST?
With regard table cache: I suggest set a high value. With 3,000 queries per hour you're not remotely near the problem of having a problem with that. Why don't you set: table_open_cache=2048 table_definition_cache=512 (even though you may only have a couple tables)
With regard the advice about open tables, you should be more interested in the number of tables opened per second (or minute, whatever) in any given time period, rather than looking at the overall number of tables opened since the server started to run. So you should monitor your opened_tables status variable every few minutes, and do the math. You would typically want to see less than 1 table opened in a second. Again, I'm throwing numbers around, but just to give you proportion. The open_tables is nice to know, as well. On good usage it can be very high, and that means your server is actively reading from tables. Fine! As long as the number of opened_tables/sec does not rise too high, this is no worry.
There could be other reasons for "too many connections". Are the queries very long? Such that they just accumulate? Are some background tasks failing to complete? (You do have a wait_timeout=300 which is good; however some PHP job might be issuing new queries every few seconds, failing to close properly?
Noting again: watch over SHOW PROCESSLIST and see what comes up.

Mysql – One to many join, Sum from both table

Suppose your date range is 2014-12-08 to 2014-12-11

This would be the Query you need

SELECT B.*,A.quantity
FROM
(
    SELECT
        AA.id,SUM(BB.quantity) quantity
    FROM
    (
        SELECT id FROM orders
        WHERE date >= '2014-12-08' AND date <= '2014-12-11'
    ) AA INNER JOIN orderdetails BB ON AA.id = BB.order_id
    GROUP BY AA.id
) A INNER JOIN orders B USING (id);

Best Answer

Related Solutions

Mysql – thesql fine tuning: open_tables, opened_tables

Mysql – One to many join, Sum from both table

Related Question