Outer Join in mYsql Qyery

Question

I have folowing MYsql Query And Trying to right outer join but unable to understan how to do this

here is query plase any one help

select lp_des.lpname,today.cnt_veh_tdy,todate.cnt_veh_tdate
from
(select distinct registration.lpcode,loadingpoint.lpname 
from registration,loadingpoint
where registration.lpcode=loadingpoint.lpcode) lp_des,
(select lpcode,count(vehicleno) cnt_veh_tdate
from registration
where registration.companycode='01'
group by lpcode) todate,
(
select lpcode,count(vehicleno) cnt_veh_tdy
from registration
where registration.companycode='01'
and registration.date=(select max(date) from registration)
group by lpcode) today
right outer join today on lp_des.lpcode = today.lpcode
right outer join todate on lp_des.lpcode = todate.lpcode

I want to make right outer join on this part

where lp_des.lpcode=todate.lpcode
and   lp_des.lpcode=today.lpcode

Please help and Thanks in advance

Does it have to be a `right outer join`? Maybe you should describe what is the result you want, instead of posting only the solution you think you need. — GolezTrol, Mar 31 '12 at 06:49
Also, about the whole world uses `left join` (=`left outer join`). Every right join on A and B can be rewritten to a left join B and A, which makes it more readable. Especially mixing right joins and left joins in one query makes it harder to read, test and modify. — GolezTrol, Mar 31 '12 at 06:51
I have made right outer join in above query please now chek what may be syntax error in query — Adeel Aslam, Mar 31 '12 at 06:51
Was it really easier to post that whole chunk of code here instead of just checking the manual on `right join`? — GolezTrol, Mar 31 '12 at 06:57
I am using Query and Subquery too thats i show up my whole query so it will easy to undertand i still not got my answer please change my query to actual outer join — Adeel Aslam, Mar 31 '12 at 07:04

score 0 · Answer 1 · answered Mar 31 '12 at 06:48

0

The syntax for a right outer join is:

SELECT t1.id, t2.id FROM t1 RIGHT OUTER JOIN t2 ON t1.field1 = t2.field2

If you're joining on the same field you can use USING instead of ON:

SELECT t1.id, t2.id FROM t1 RIGHT OUTER JOIN t2 USING (field)

answered Mar 31 '12 at 06:48

Michael

11,912
6
49
64

Kindly chek my query and make outer join in that query ur example is not working – Adeel Aslam Mar 31 '12 at 07:11

score 0 · Accepted Answer · answered Mar 31 '12 at 07:19

You asked for this:

select 
  lp_des.lpname,
  today.cnt_veh_tdy,
  todate.cnt_veh_tdate
from
  (select distinct 
    r.lpcode,
    l.lpname 
  from 
    registration r
    inner join loadingpoint l on l.lpcode = r.lpcode) lp_des
  right join
    (select 
      r.lpcode,
      count(r.vehicleno) cnt_veh_tdate
    from 
      registration r
    where 
      r.companycode='01'
    group by 
      lpcode) todate on todate.lpcode = lp_des.lpcode
  right join
    (select 
      r.lpcode,
      count(r.vehicleno) cnt_veh_tdy
    from 
      registration r
    where 
      r.companycode = '01'
      and registration.date = (select max(date) from registration)
    group by 
      r.lpcode) today on today.lpcode = lp_des.lpcode

But I think you mean this:

select
  r.lpcode,
  l.lpname,
  count(r.vehicleno) cnt_veh_tdate,
  count(case when r.date = md.date then r.vehicleno else null end) cnt_veh_tdy
from
  registration r
  inner join (select max(rm.date) maxdate from registration rm) md
  left join loadingpoint l on l.lpcode = r.lpcode
where
  r.companycode = '01'
group by 
  r.lpcode

and maybe even this:

select
  r.lpcode,
  l.lpname,
  count(r.vehicleno) cnt_veh_tdate,
  count(case when r.date = date() then r.vehicleno else null end) cnt_veh_tdy
from
  registration r
  left join loadingpoint l on l.lpcode = r.lpcode
where
  r.companycode = '01'
group by 
  r.lpcode

If I read it correctly, you want a query that returns the number of vehicles for company 1 assigned to a loading point, overall as well as for today only. And you also want that count for vehicles that do not have loading point assigned yet. Though it would help if you would have added this description. It will help the ones answering your question, but it will also help you writing the right query in the first place.

Outer Join in mYsql Qyery

2 Answers2