SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: limit results

  1. #1
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    limit results

    I have a db table of scores of teams...

    Date, Team Away, Team Away Score, Team Home, Team Home Score

    I want to select only games where both teams appear at least in 8 rows (doesnt matter if they were away or home).

    How would I do this select?

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you will have to do it in two steps
    First, get all team combinations with at least 8 scores,
    something like this:

    select
    min(team_away) as team1,
    max(team_home) as team2,
    case
    when team_away < team_home then concat(team_away, '/', team_home)
    else concat(team_home, '/', team_away)
    end as team_comb,
    count(*) as num_of_scores
    from scores
    group by team_comb
    having count(*) >= 8

    Second, for each combination, get the scores:

    --@team1 and @team2 are the current values in the first result
    select *
    from scores
    where team_away in(@team1, @team2) and team_home in(@team1, @team2)

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it would be a lot easier, jkh1978, if you told us about the Team table too

    simply join Team to Scores on either home id or away id, and do a group by having count(*)>=8

    rudy
    http://rudy.ca/

  4. #4
    SitePoint Evangelist jkh1978's Avatar
    Join Date
    May 2001
    Location
    Northern Virginia
    Posts
    445
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I know this isn't the best solution, but there is only one table currently (I did not design the database).

    I may have stated the question incorrectly, because I think jofa did not provide what i want (but probably answered what I asked).

    I want all rows from the scores table where each team appears in at least 8 rows. These teams do not need to play each other 8 times.

    If Virginia Tech had 4 away games and 4 home games, they meet the criteria. If Syracuse has 6 away games and 5 home games, they meet the criteria. If Temple played 1 home game, they do not meet the criteria.

    So I would want the row which included Virginia Tech and Syracuse, but do not include the game where Syracuse played Temple.

  5. #5
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, in that case my answer is:

    create temporary table teams1 (team int);

    insert into teams1
    select team_away from scores;

    insert into teams1
    select team_home from scores;

    create temporary table teams2 (team int);

    insert into teams2
    select team from teams1
    group by team having count(*) >= 8;

    drop table teams1;

    select distinct date, team_away, team_home
    from scores s, teams2 t
    where s.team_away = t.team or s.team_home = t.team;

    drop table teams2;


    You could use "union" to insert team IDs into teams1 (implemented in MySql 4.0.0), but you cannot use "group by" on the result from a union (that's why we need teams2)

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    nice job, jofa

    illustrating once again the importance of good data modelling before building the database

    i mean, heck, with just that one table, there's no integrity, and you could have Vilanova and Villanova as two separate teams

    to say nothing of the efficiency of creating and dropping tables on the fly just to do an information request...

    rudy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •