Highlighted

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

Explorer ,
Apr 04, 2015

Copy link to clipboard

Copied

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?

Views

122

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

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

Explorer ,
Apr 04, 2015

Copy link to clipboard

Copied

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?

Views

123

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Apr 04, 2015 0

Have something to add?

Join the conversation