database - How to join multiple tables in MySQL 2? -

i want see citizen's full name

where notifyemployee ='1' in reportbasket,reportnobasket tables

and reportnobasket.groupemail not null

and count email reportbasket, citizenemail reportnobasket in 1 counter

tables:

citizen(email, firstname,lastname)

reportbasket(email,notifyemployee)

reportnobasket(citizenemail, notifyemployee , groupemail)

before represent query show email in way, working :

select email,count(email) email_count  (     select email     reportbasket     notifyemployee='1'     union      select citizenemail     reportnobasket     notifyemployee='1' ) t group email order email asc  

but want show citizen's full name instead of email address e.g. concat(citizen.firstname,' ' citizen.lastname)

how can join ?

try this:

select concat(firstname, ' ', lastname), count(*) citizen_count  (     select c.firstname, c.lastname     reportbasket rb     join citizen c         on rb.email = c.email     notifyemployee='1'     union      select c.firstname, c.lastname     reportnobasket rnb     join citizen c         on rnb.citizenemail = c.email     notifyemployee='1' ) t group firstname, lastname order firstname, lastname asc