Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    2

    Unanswered: Problem with a join

    Hello,

    I'm stuck with a problem i think it's a complex join.
    Try to make a competition-scheme

    Tables:
    Clubs with fields: id, name
    1 Arsenal
    2 Barcelona
    3 Munchen
    4 Ajax

    Games with fields: id date club1id club2id goalsclub1 goalsclub2
    1 jan1 1 2 0 0
    2 jan2 3 4 0 0
    3 jan3 1 3 0 0

    as a result i want:

    Date Home Visitors result
    jan1 Arsenal Barcelona 0 - 0
    jan2 Munchen Ajax 0 - 0
    jan3 Arsenal Munchen 0 - 0

    I tried the following but it don´t get me in the right direction..

    SELECT games.data, games.goalsclub1, games.goalsclub2, clubs,name
    FROM games LEFT JOIN clubs
    ON games.club1id = clubs.id

    Hopefully someone can help me with the good select and join and perhaps some explanation for a newbee.

    regards
    Jan-Martin

  2. #2
    Join Date
    Apr 2005
    Location
    Lier, Belgium
    Posts
    122
    Quote Originally Posted by jan-martin
    Try to make a competition-scheme

    as a result i want:

    Date Home Visitors result
    jan1 Arsenal Barcelona 0 - 0
    jan2 Munchen Ajax 0 - 0
    jan3 Arsenal Munchen 0 - 0
    Code:
    SELECT
    	g.date AS Date,
    	home.name AS Home,
    	away.name AS Visitors,
    	CONCAT(g.goalsclub1, ' - ', g.goalsclub2) AS Result
    FROM Games AS g
    JOIN Clubs AS home ON g.club1id = home.id
    JOIN Clubs AS away ON g.club2id = away.id
    ORDER BY g.date;
    --
    felix

  3. #3
    Join Date
    Dec 2005
    Posts
    2

    Smile

    @felixq

    Thank you for the code. It worked for me when i changed the join-line to:
    LEFT JOIN .........

    This opened a new view for me to use the AS.

    thank you for your quick reply

    Jan-Martin

Posting Permissions

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