help returning a query from two tables

[Anon]

I have two tables which I am using to run a query on.

Matches (MatchID, DateTime, Competition, Opposition)

Goals (MatchID, PlayerID, Minute)

What I would like is to have a query that selects Matches.Opposition, Matches.Competition and a count of how many times the MatchID is present in the Goals table.

I have tried this but it does not return any results.

SELECT Matches.MatchID, Matches.Competition, COUNT(Goals.MatchID) FROM Matches, Goals WHERE Matches.MatchID = Goals.MatchID GROUP BY Matches.MatchID;

If anyone can help I would be very grateful.

Thanx,

Ben.

[merkinmuffley]

select matches.matchid, matches.competition, (select count(goals.matchid) from goals where goals.matchid=matches.matchid) goalcount from matches

that's just one way- subquery for the # of goals for each match.

you are pretty close to having a working query there, and I think you just need to substitute matches.matchid or matches.competition for goals.matchid in the call to count(). also add competition to your group by clause.

select m.matchid, m.competition, count(m.matchid) from matches m, goals g where m.matchid=g.matchid group by m.matchid, m.competition

hth
/nick