MySQL: Avarge and join two querries

Question

I have two queries:

SELECT
  s.id AS id,
  g.id AS group_id,
  g.nazwa AS group_name,
  s.nazwa AS station_name,
  s.szerokosc AS szerokosc,
  s.dlugosc AS dlugosc,
  s.95 as p95,
  s.98 as p98,
  s.Diesel as diesel,
  s.DieselTIR as dieseltir,
  s.Super98 as s98,
  s.SuperDiesel as sdiesel,
  s.LPG as lpg,
  s.ulica as ulica,
  s.kodPocztowy as kod_pocztowy,
  s.miasto as miasto,
  w.id as wojewodztwo_id,
  w.nazwa as wojewodzto_nazwa,
  k.id as kraj_id,
  k.kod as kraj_kod,
  k.nazwa as kraj_nazwa,
  s.data as date_mod,
  s.active as active
FROM stacje_main s
JOIN stacje_grups g ON (s.grupa=g.id)
JOIN wojewodztwa w ON (s.wojewodztwo=w.id)
JOIN kraje k ON  (w.kraj=k.id)
WHERE s.data > 0;

and

SELECT
  AVG(rr.vote) as avarge,
  COUNT(rr.station_id) counter
FROM stacje_ratings rr
GROUP BY rr.station_id;

and in second querry not all id (station_id) are present, and sometimes are doubled. there is need to join station_id with id, and give average value of rate for each id.

The problem that when no rate, the value in question in average and counter have to be 0.

When i combined this queries i see only this ID, that has present station_id. But i want to see all.

Answer 1

Dear bro, please use left / right join according to your need, you have used inner join so so it will search for same records present in both tables.

select * frm tbl right join tbl1 on tbl1.id = tbl.id

if suppose your right table then if no matching record isn't present in tbl1 then also all those records are populated

same case in left join but reverse the table name only...

:)

Answer 2

You need to use a LEFT JOIN (see MySQL JOIN syntax).

This will return NULL for rows that have no matching row in the joined table, so I use COALESCE to replace them by 0.

SELECT
  s.id AS id,
  g.id AS group_id,
  -- [...]
  COALESCE( x.average, 0 ) AS average
  COALESCE( x.counter, 0 ) AS counter
FROM stacje_main s
JOIN stacje_grups g ON (s.grupa=g.id)
JOIN wojewodztwa w ON (s.wojewodztwo=w.id)
JOIN kraje k ON  (w.kraj=k.id)
LEFT JOIN (
  SELECT
    rr.station_id
    AVG(rr.vote) as avarge,
    COUNT(rr.station_id) counter
  FROM stacje_ratings rr
  GROUP BY rr.station_id
) x ON ( x.station_id = s.id )
WHERE s.data > 0;