Results 1 to 2 of 2

Thread: SQL union

  1. #1
    Join Date
    Nov 2016
    Posts
    1

    Unanswered: SQL union

    I am making a small soccer prediction website and one functionality I need is to generate a league table from my database that contains past results. My db has 5 columns;

    Fixture_ID.

    Home_team.

    Away_team.

    Htgoals( Number of goals scored by home team).

    Atgoals( Number of goals scored by away team).

    I have two queries that return the league tables for teams only when playing at home and playing away from home


    Home-" Select results.Home_team,
    SUM(if(results.Htgoals > results.Atgoals,3,0)) AS W,
    SUM(IF(results.Htgoals = results.Atgoals,1,0)) AS D,
    SUM(IF(results.Htgoals < results.Atgoals,1,0)) AS L
    from results
    GROUP BY results.Home_team
    order by W desc";

    Away table-" Select results.Away_team,
    SUM(if(results.Atgoals > results.Htgoals,3,0)) AS W,
    SUM(IF(results.Atgoals = results.Htgoals,1,0)) AS D,
    SUM(IF(results.Atgoals < results.Htgoals,1,0)) AS L
    FROM results
    GROUP BY results.Away_team
    ORDER BY W DESC";



    How can I;

    1.Do a union to generate an overall league table

    2.Rank teams by the number of points rather than number of wins

    3. I would like to include a fixtures table in the db, what is the best way of making the db (fixtures and results table) relational (foreign keys)

  2. #2
    Join Date
    Jan 2017
    Posts
    10
    SQL UNION Syntax:

    SELECT column_name(s) FROM table1
    UNION
    SELECT column_name(s) FROM table2;

Tags for this Thread

Posting Permissions

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