0 Replies Latest reply on Apr 4, 2015 3:30 PM by College Kid

    matching records from two tables even when the ID of a person is null.

    College Kid

      I have a MySQL named Natural Systems which is a magazine account that it member's will both sell handmade items wherein the proceeds will go into the Natural Systems account and each member also give a weekly donation. All of the proceeds will help toward the printing of the subsequent volumes. both of the selling of handmade items and the weekly dues  happens during the same week or the same magazine volume number. I am trying to write a query that will pull both of the sales as well as the weekly dues payments weather they have paid or not.


      SELECT foiid, trim(concat(name.fname,' ',name.lname)) AS no_ns, nspayamt, DATE_FORMAT(nspaydate, '%m/%d/%Y') FROM name LEFT JOIN nspay ON nspayfoiid = foiid and volume  = '27' WHERE nspayfoiid is null AND  type = 'Registered' AND city = 'richmond' AND status = 'a' group by foiid ORDER BY no_ns

      This code will bring up the following names of those who did not pay weekly dues during volume 2:

      Christopher Gabb

      Michael banks
      Timothy Hardin


      However there is another table I wish to call the same Individuals who sold magazine during the same week of volume 27

      SELECT trim(CONCAT(name.fname,' ',name.lname))AS Sold,
      round(sum(fcnsales.salesamt)) as TOTAL_Mags_SOLD FROM name, fcnsales WHERE fcnsales.salesfoiid = name.foiid
      AND fcnsales.salesvolume '27'  GROUP BY name.foiid ORDER BY TOTAL_mags_SOLD desc;

       

      This above code produces the following results:

      Sold                             TOTAL_Mags_SOLD  

      Christopher Gabb        50

      Michael banks             36       
      Timothy Hardin           12

       

      I would like to combine the two queries to produce the following

       

      Sold                             TOTAL_Mags_SOLD   nspayamt

      Christopher Gabb          50                                none    

      Michael banks               36                                none
      Timothy Hardin            12                               none

       

       

      can anyone give me a direction to go to?