Complex mysql query probably build subquery

Question

In my case, I have data from multiple tables that I want to extract a single query. It's about football statistics. In one minute table recorded the dates of the matches and the other recorded data and results of the matches. The problem is that I want to limit the applications of dates, not the number of matches, as in a day has a few games. Managed to build a complex query that displays all my data, but it displays the results on the number of games rather than the dates so I can not use limitation, because eating in this case becomes more games rather than dates. My question is is it possible to build an application that has a limitation on the dates and at the same time to display the results of all matches played in the dates? Here is the code of the application that I use now:

SELECT
            MAIN.id,
            SECTION.type,
            MAIN.date as date_,
            MAIN.prognosis,
            HOME_TEAM.team_name as home_team,
            GUEST_TEAM.team_name as guest_team,
            FIRST_INDEX.index as f_index,
            SECOND_INDEX.index as s_index,
            THIRD_INDEX.index as t_index,
            DATA.home_result,
            DATA.guest_result,
            DATA.coefficient,
            DATA.success,
            MAIN.total_coefficient,
            MAIN.total_success

        FROM ssdt_matches_main as MAIN

        LEFT JOIN ssdt_section_type as SECTION ON (MAIN.type_id = SECTION.id)
        LEFT JOIN ssdt_matches_data as DATA ON (DATA.matches_main_id = MAIN.id )
        LEFT JOIN ssdt_matches_teams as HOME_TEAM ON (HOME_TEAM.id = DATA.home_team_id )
        LEFT JOIN ssdt_matches_teams as GUEST_TEAM ON (GUEST_TEAM.id = DATA.guest_team_id )
        LEFT JOIN ssdt_matches_index as FIRST_INDEX ON (FIRST_INDEX.id = DATA.first_index_id )
        LEFT JOIN ssdt_matches_index as SECOND_INDEX ON (SECOND_INDEX.id = DATA.second_index_id )
        LEFT JOIN ssdt_matches_index as THIRD_INDEX ON (THIRD_INDEX.id = DATA.third_index_id ) 


        WHERE SECTION.type = 'Risk prognosis'


        ORDER BY MAIN.id DESC

Gordon Linoff · Accepted Answer · 2013-08-06 13:21:33Z

1

You want to limit the dates in a where clause like:

where MAIN.date between date('2012-01-01') and date('2012-12-31');

If you want to get the records from, say, the most recent 10 days (with a match), you can do something like this:

select . . .
from . . . join
     (select date
      from ssdt_matches_main md
      group by date
      order by date desc
      limit 10
     ) datel
     on datel.date = MAIN.date

This uses a join to select a list of dates and then a join to do the filtering.

EDIT:

Your from clause would look like:

    FROM ssdt_matches_main as MAIN

    LEFT JOIN ssdt_section_type as SECTION ON (MAIN.type_id = SECTION.id)
    LEFT JOIN ssdt_matches_data as DATA ON (DATA.matches_main_id = MAIN.id )
    LEFT JOIN ssdt_matches_teams as HOME_TEAM ON (HOME_TEAM.id = DATA.home_team_id )
    LEFT JOIN ssdt_matches_teams as GUEST_TEAM ON (GUEST_TEAM.id = DATA.guest_team_id )
    LEFT JOIN ssdt_matches_index as FIRST_INDEX ON (FIRST_INDEX.id = DATA.first_index_id )
    LEFT JOIN ssdt_matches_index as SECOND_INDEX ON (SECOND_INDEX.id = DATA.second_index_id )
    LEFT JOIN ssdt_matches_index as THIRD_INDEX ON (THIRD_INDEX.id = DATA.third_index_id ) 
    join (select date
          from ssdt_matches_main md
          group by date
          order by date desc
          limit 10
         ) datel
         on datel.date = MAIN.date

edited Aug 6, 2013 at 13:21

answered Aug 6, 2013 at 11:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2656529 Over a year ago

I want to limit the data on number of dates, not dates From-To. Something like LIMIT 0,50

Gordon Linoff Over a year ago

@user2656529 . . . If you want to limit to a given number of dates, then the second version should work.

user2656529 Over a year ago

I think I understand the logic of what I propose as a solution. I'll try to return a result. What I do not understand is where to insert this JOIN and where others remain LEFT ION. This is where a little confused ... I have not done subqueries

user2656529 Over a year ago

This doesn't works, and not give me the expected result. The first thing I noticed is that the date field is not in this table "ssdt_matches_data" but in the other table "ssdt_matches_main". And the way it is, I want the box to be sorted is ID field instead of the Date field. Something I could not get it to work properly

Gordon Linoff Over a year ago

@user2656529 . . . Can you edit your question to provide sample data and desired results? I may not understand the problem correctly.

user2656529 · Accepted Answer · 2013-08-06 21:06:19Z

I fixed the query. I would not have done it without your help. Thank you very much!

SELECT
            MAIN.id,
            SECTION.type,
            MAIN.date as date_,
            MAIN.prognosis,
            HOME_TEAM.team_name as home_team,
            GUEST_TEAM.team_name as guest_team,
            FIRST_INDEX.index as f_index,
            SECOND_INDEX.index as s_index,
            THIRD_INDEX.index as t_index,
            DATA.home_result,
            DATA.guest_result,
            DATA.coefficient,
            DATA.success,
            MAIN.total_coefficient,
            MAIN.total_success

        FROM ssdt_matches_main as MAIN

LEFT JOIN ssdt_section_type as SECTION ON (MAIN.type_id = SECTION.id)
LEFT JOIN ssdt_matches_data as DATA ON (DATA.matches_main_id = MAIN.id )
LEFT JOIN ssdt_matches_teams as HOME_TEAM ON (HOME_TEAM.id = DATA.home_team_id )
LEFT JOIN ssdt_matches_teams as GUEST_TEAM ON (GUEST_TEAM.id = DATA.guest_team_id )
LEFT JOIN ssdt_matches_index as FIRST_INDEX ON (FIRST_INDEX.id = DATA.first_index_id )
LEFT JOIN ssdt_matches_index as SECOND_INDEX ON (SECOND_INDEX.id = DATA.second_index_id )
LEFT JOIN ssdt_matches_index as THIRD_INDEX ON (THIRD_INDEX.id = DATA.third_index_id ) 
JOIN (SELECT id
      FROM ssdt_matches_main md
      WHERE type_id = 2
      ORDER BY id DESC
      LIMIT 0,5
     ) datel
     ON datel.id = DATA.matches_main_id


        ORDER BY MAIN.id DESC

Collectives™ on Stack Overflow

Complex mysql query probably build subquery

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related