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

--
felix

@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