Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2012
    Posts
    44

    Unanswered: Simple structure question

    Let's say I would like a table (table1) with fixtures and results for some kind of sport. I have one table with team id's as well (table2), containing team information such as their name, etc.

    In this table1, my first thought would be to have fields like:
    table1:
    hometeamid
    awayteamid
    homescore
    awayscore

    So one row in table1 equals one fixture.

    table2 could then look like:
    teamid
    teamname

    However, when doing an output of this result, linking table1's home- and awayteamid with table2's teamname, I end up with a non-effective query right?

    Sorry if this is a stupid question, but should I then do the structure differently?

    Thanks for any help, really appreciate it.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Haydn View Post
    However, when doing an output of this result, linking table1's home- and awayteamid with table2's teamname, I end up with a non-effective query right?
    wrong

    your structure is fine (although it can be improved slightly in a manner which has no bearing on this thread)

    do a search for home team away team query in this web site or any other

    this problem has been asked and solved numerous times

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm not sure what you mean by "a non-effective query"...

    Why don't you try and write the query you are wanting and post that here, along with sample data and desired results
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    by "non-effective" he meant that he was able to get the team name for only one of the two teams

    what he's missing is the idea of joining to the teams table twice

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2012
    Posts
    44
    Quote Originally Posted by r937 View Post
    by "non-effective" he meant that he was able to get the team name for only one of the two teams

    what he's missing is the idea of joining to the teams table twice

    I miss a lot of things alright...a red-face smiley should be present now!

    I realized I got the answer in another topic I wrote a couple of days ago, LINK

    It works perfect with that solution. I didn't know it was possible to join twice in that manner, to have two different aliases.

    Thanks for your help!

  6. #6
    Join Date
    Feb 2012
    Posts
    44
    I tried to search in the forums but didn't find an answer to the following question. It's related to my old topic here, so I post it here instead of creating a new one.

    Consider the following query (with the structure from the first post in this topic in mind).

    Code:
    SELECT homescore, awayscore
    FROM table1
    WHERE hometeamid = 1 OR awayteamid = 1
    Now, I would only like the query to return the score for team id 1. Is it possible to make the SELECT statement dynamic, so it only returns a field depending on whether team id 1 is in the hometeamid or the awayteamid field?

    So what I really want is something like SELECT homescore OR awayscore...

    Of course this can be done in PHP afterwards, but it would be really nice if it's possible to do in the query.

    I hope it's understandable, thanks!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT homescore
      FROM table1
     WHERE hometeamid = 1 
    UNION ALL
    SELECT awayscore
      FROM table1
     WHERE awayteamid = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2012
    Posts
    44
    Wow, that was fast, thanks! It works great!

    What if I want a common condition for both queries as well, let's say I would like the 5 latest games, and I have a field called date_played which I can ORDER it from, is that possible?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, it's possible

    why don't you try it and see what happens

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2012
    Posts
    44
    Quote Originally Posted by r937 View Post
    yeah, it's possible

    why don't you try it and see what happens

    Thanks!

    So this works fine:

    Code:
    SELECT homescore
      FROM table1
     WHERE hometeamid = 1 
    UNION ALL
    SELECT awayscore
      FROM table1
     WHERE awayteamid = 1
    ORDER BY date_played DESC LIMIT 5
    So the system is ordering the both separate queries , that's great!

    Let's say I have a leagueid here as well, do I have to have it in both queries or is there another way?

    Code:
    SELECT homescore
      FROM table1
     WHERE hometeamid = 1 AND leagueid = 1
    UNION ALL
    SELECT awayscore
      FROM table1
     WHERE awayteamid = 1 AND leagueid = 1
    ORDER BY date_played DESC LIMIT 5
    Because the query below will not be correct:

    Code:
    SELECT homescore
      FROM table1
     WHERE hometeamid = 1 
    UNION ALL
    SELECT awayscore
      FROM table1
     WHERE awayteamid = 1
    AND leagueid = 1
    ORDER BY date_played DESC LIMIT 5
    Thanks.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Haydn View Post
    Let's say I have a leagueid here as well, do I have to have it in both queries
    i am sure you found the answer by testing...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2012
    Posts
    44
    Quote Originally Posted by r937 View Post
    i am sure you found the answer by testing...

    He's pedagogical!

    I've tested and I think I need to have the condition in both queries, I haven't found anything else yet.

    While I continue to test, I have another question. What if I want to JOIN a table2 (as described in the first post) and get the team names as well?

    This doesn't work:

    Code:
    SELECT a.homescore, b.teamname, bb.teamname AS oppteam
      FROM table1 a
    JOIN table2 b ON a.hometeamid = b.teamid
    JOIN table2 bb ON a.awayteamid = bb.teamid
     WHERE hometeamid = 1 AND leagueid = 1
    UNION ALL
    SELECT a.awayscore, b.teamname, bb.teamname AS oppteam
      FROM table1 a
    JOIN table2 b ON a.awayteamid = b.teamid
    JOIN table2 bb ON a.hometeamid = bb.teamid
     WHERE awayteamid = 1 AND leagueid = 1
    ORDER BY date_played DESC
    Would appreciate some kind of hint!

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i am sorry, i am not familiar with the "this doesn't work" error message

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2012
    Posts
    44
    Quote Originally Posted by r937 View Post
    i am sorry, i am not familiar with the "this doesn't work" error message

    When I tried this again, it worked.

    Quote Originally Posted by r937 View Post
    i am sure you found the answer by testing...

    Is it correct that I had to have the condition in both queries as I posted above? I didn't find any other solution.

    Thanks!

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Haydn View Post
    Is it correct that I had to have the condition in both queries as I posted above?
    yes

    notice that the result set which includes results from both queries in the union nevertheless still has column names which come only from the first query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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