[SOLVED] Select Query with 2 Tables joined doing a function of count confusing

[sspoke]

Hello,

 

I want to do what title says

 

anyways Here is the layout

 

Table1->gametypes->Columns->   id(primary,auto ++) and gamename(text)

Table2->matches->Columns->   id(primary,auto++) and gameid(int) which is the ID that matches Table1 id and a bunch of other columns which I don't have to list

 

everytime someone makes a match from my program it would insert query into Table2

 

but if someone wants to lets say see the amount of rooms made per each catagory in 1 return of gametypes how would I do that in 1 Query  I could do it in 2 queries but I really need this in 1 query..

 

this is what I got

 

SELECT * FROM gametypes INNER JOIN (SELECT Count(gameid) FROM matches WHERE gametypes.id = matches.gameid)

 

I missed the ON operator somewhere but ya lol confusing..

 

It gives a sql error  Every dervied table must have its own alias.. 

 

Thanks for any help

[shoz]

but if someone wants to lets say see the amount of rooms made per each catagory in 1 return of gametypes how would I do that in 1 Query  I could do it in 2 queries but I really need this in 1 query..

 

If this is not what you're looking for, explain what a "category" and a "room" is. The description of the tables don't give much indication as to what you mean by the two.

 

SELECT
g.*, COUNT(m.gameid) AS num
FROM
gametypes AS g
LEFT JOIN
matches AS m
ON
g.id = m.gameid
GROUP BY
g.id

[sspoke]

ya that works perfectly exactly what i wanted someone told me i needed some self join

 

Many Thanks