Left join three tables with inner join on two in mysql

122
March 05, 2019, at 06:30 AM

I have five tables

tableevents
eventname eventid openingdate eventtype
alpha      2222    2019-08-07  44
beta      22299    2019-08-09  48
gama      24555    2019-06-9   47
tablesectora
eventid resultstatus liability userid  profitsectora
tablesectorb
eventid resultstatus liability userid    profitsectorb
tablesectorc
eventid resultstatus liability userid    profitsectorc
users
userid registrar_id 
io      manager
co      manager

what I am looking is following thing for regsitar_id suppose (manager)

1-show data in manager dashboard if any userid (whose registar id is manager) has data in one of three or in two or in all tables tablesectora,tablesectorb and tablesectorc (three table can have same eventid and cannot have same eventid) 2- also show the eventname and ,eventid and opening date

SELECT distinct tl.eventid eventid ,ul.resultstatus resultstatus,ce.eventname eventname,ce.opendate opendate,ce.eventtypeid eventtypeid ,u.registrar_id from users u, tablesectora tl left join tablesectorb uf on uf.eventid=tl.eventid left join tablesectorc ul on ul.eventid=tl.eventid left join tableevents ce on ce.eventid=ul.eventid and u.userid=tl.userid and where u.registar_id='manager'

The query is working but I am not able to get the expected output

EXpected output should be

if only tablesectora has data

tablesectora

eventid resultstatus liability userid  profitsectora 
2222       complted   2000      io       20000   

Output

eventname eventid openingdate resultstatus  eventtype
alpha      2222    2019-08-07  completed     44

if tablesectora and b both have data

tablesectora

eventid resultstatus liability userid  profitsectora 
2222       complted   2000      io       20000  

tablesectorb

eventid resultstatus liability userid  profitsectorb
2222       complted   200000     io       200000 
22299      completed  555666     co       56666 

Output

eventname eventid openingdate resultstatus  eventtype
alpha      2222    2019-08-07  completed     44
beta       22299   2019-08-09   completed    48

so you see I just want theaccumulated eventid ,eventname ,eventtype and ,resultstatus (resultstatus will be same for evenry eventid in every table

Answer 1

It doesn't matter in which of the three sector tables an event occurs. So you can treat the tables as one by gluing them together with UNION ALL. (To me it even looks like a bad data model, and you should rather have one table with a sector type column instead of one table per sector.)

Glue the three tables' rows together, limit to managers, aggregate over event ID and join.

select e.eventname, e.eventid, e.openingdate, e.eventtype, s.resultstatus
from tableevents e
join
(
  select eventid, max(resultstatus) as resultstatus
  from
  (
    select eventid, resultstatus, userid from tablesectora
    union all
    select eventid, resultstatus, userid from tablesectorb
    union all
    select eventid, resultstatus, userid from tablesectorc
  ) glued
  where userid in (select userid from users where registrar_id = 'manager')
  group by eventid
) s on s.eventid = e.eventid
order by e.eventid;
Answer 2

You should use DISTINCT on a SUBQUERY with a UNION statement to achieve this:

SELECT DISTINCT eventname
      ,eventid 
      ,openingdate 
      ,resultstatus  
      ,eventtype
FROM (SELECT eventname
          ,A.eventid 
          ,openingdate 
          ,resultstatus  
          ,eventtype
        FROM  tableevents A
        INNER JOIN tablescorea B
        ON    A.eventid = B.eventid
        INNER JOIN users C
        ON C.userid = A.userid
      UNION SELECT DISTINCT eventname
          ,A.eventid 
          ,openingdate 
          ,resultstatus  
          ,eventtype
      FROM  tableevents A
      INNER JOIN tablescoreb B
      ON    A.eventid = B.eventid
      INNER JOIN users C
        ON C.userid = A.userid
      )
Rent Charter Buses Company
READ ALSO
Add an extra column in select with '*'

Add an extra column in select with '*'

I am writing a query sql for a data migration job to fetch data and send from mysql-server a to mysql-server b

125
default value doesn't work for sqlalchemy.Column

default value doesn't work for sqlalchemy.Column

I'm trying using aiomysql+sqlalchemy according to aiomysqlsa, but hit problem when using the default parameter

111
Selecting rows which fulfill missing amount of attributes of table?

Selecting rows which fulfill missing amount of attributes of table?

Suppose I have list of attributes represented as each column of table having table of foods are as below,

116
Storing Questions in DB

Storing Questions in DB

I want to store questions/options that is a combination of text, image, expression, etc just like any visual reasoning questionWhat would be the best way to store them? Either whole as an image or is there any other way

117