SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist
    Join Date
    Jul 2000
    Location
    Warwickshire, England
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    a join, sub query or two different queries?

    Hello again,

    I have two tables:
    Teams (teamId, position, ....)
    Fixtures (fixtureId, challengingTeamId, challengedTeamId, date, ...)

    What would be the best way to select a team's row with an additional field for whether they have a challenge for this week? (in this competition, there is at most one fixture per week) The aim is to display a list of teams together with whether or not they have a challenge for the current week

    The queries to get the two things separately are straightforward ...
    just to clarify what I am after, this weeks fixtureId for a teamId %1\$d is
    Code:
    SELECT COUNT(*) FROM FIXTURES 
    WHERE 
    (ChallengingTeamId = %1\$d OR ChallengedTeamId = %1\$d) AND 
    (YEARWEEK( date, 7 ) = YEARWEEK( CURRENT_DATE, 7 ))
    If possible I'd like to keep it in one query.
    (currently I am select'ing the teams in position order then determining if they have a challenge this week one at a time using the above query but this is a bit slow. This query will be run a fair few times per day, not just once a season like the other ones I asked help for!)

    thanks, Peter

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Jul 2000
    Location
    Warwickshire, England
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks Rudy,

    due to my general lack of knowledge of sql the the below is my best stab
    Code:
    SELECT teamId, IF(YEARWEEK( FIXTURES.date, 7 ) = YEARWEEK( CURRENT_DATE, 7 ), 1, 0) as got_fixture
    FROM TEAMS
    LEFT JOIN FIXTURES ON TEAMS.teamId = FIXTURES.challengingTeamId OR TEAMS.teamId = FIXTURES.challengedTeamId
    this would be fine of course if there was at most one fixture in the db, but there's lots! could you help? (I only want each team to have one row)

    thanks for your help, Peter

  4. #4
    SitePoint Evangelist
    Join Date
    Jul 2000
    Location
    Warwickshire, England
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I finally got it for future reference

    Code:
    SELECT * 
    FROM TEAMS
    LEFT JOIN FIXTURES ON (TEAMS.teamId = FIXTURES.challengingTeamId OR TEAMS.teamId = FIXTURES.challengedTeamId) AND YEARWEEK( FIXTURES.date, 7 ) = YEARWEEK( CURRENT_DATE, 7 )
    just in case we ever do start having e.g. twice weekly challenges is there an easy way to limit this to giving the latest challenge only if there is more than one challenge per week?

    thanks

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yeah, i wouldn't use YEARWEEK, i'd use actual dates

    ... where fixtures.`date` >= '2004-12-13' and fixtures.`date` < '2004-12-20'

    as for limiting, check out the LIMIT keyword

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Evangelist
    Join Date
    Jul 2000
    Location
    Warwickshire, England
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was under the impression that LIMIT is only for the overall number of results?

    E.g. if a team1 has two challenges (challenge1 and challenge2) this week, the above would give

    team1 challenge1 ...
    team1 challenge2 ...

    But I would like it to give only the row corresponding to the latest challenge. Can LIMIT do this?

    thanks,

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by PeterW
    I was under the impression that LIMIT is only for the overall number of results?
    yes, that's right, i misunderstood

    try this:
    Code:
    select * 
      from TEAMS
    left 
      join FIXTURES 
        on TEAMS.teamId 
           in ( FIXTURES.challengingTeamId 
              , FIXTURES.challengedTeamId )
       and FIXTURES.date
         = ( select max(FIXTURES.date)
               from FIXTURES 
              where TEAMS.teamId 
                 in ( challengingTeamId 
                    , challengedTeamId )  )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •