i have 3 tables, players, matches, wins. want make query retrieves player id, name, amount of matches played , how many wins got.
the matches table keeps track of round it's in, players take part (p_one_id , p_two_id) while wins table keeps track of match id, see won against, , player id.
if do
select players.id, players.name, count(matches.*) matches players, matches matches.p_one_id = players.id group players.id it works fine, @ least basic tests ( doesn t check wins) if do
select players.id, players.name, count(matches.*) matches, count(wins.*) wins players, matches, wins matches.p_one_id = players.id , wins.p_id = players.id group players.id i nothing, 1 reason there's no wins, d guys won, can t make sub query either, since can't use player id inside of subquery, @ least not knowledge). know query i'd need result?
i suggest using left join instead of cross join / clause option. may wish count matches player "player 2". along lines of:
select p.id, p.name, count(m1.*) + count(m2.*) matches, count(w.*) wins players p left join matches m1 on m.p_one_id = p.id left join matches m2 on m.p_two_id = p.id left join wins w on w.p_id = p.id group p.id, p.name 