MySQL INNER/LEFT JOIN on 3 tables, where records in 3rd table might not exist -

-

i've got problem can't seem figure out after bunch of failed attempts.

i've got 3 tables need join on reporting, , in 3rd table record might not exist. if record in 3rd table doesn't exist, need report null value data comes 3rd table , records match other conditions.

stripped down relevant columns, here table structures:

members - this table holds members register website

| memberid  | insertdate          | | ==========|=====================| | 1         | 2013-08-01 18:18:16 | | 2         | 2013-08-02 18:18:16 | | 3         | 2013-08-03 18:18:16 | | 4         | 2013-08-04 18:18:16 | | 5         | 2013-08-05 18:18:16 |

registration_steps - this table holds progress of registration processes , whether registration completed or not

| memberid  | completed | | ==========|===========| | 1         | 1         | | 2         | 1         | | 3         | 1         | | 4         | 0         | | 5         | 1         |

purchases - this table holds, well.. purchases

| memberid  | insertdate          | | ==========|=====================| | 1         | 2013-08-02 18:18:16 | | 1         | 2013-08-03 17:18:16 | | 1         | 2013-08-03 18:18:16 | | 5         | 2013-08-07 18:18:16 |

this query i've come far:

select `m`.`memberid`,        date(`m`.`insertdate`) `regdate`,        count(`p`.`memberid`) `totaltransactions`,        date(min(`p`.`insertdate`)) `firstpurchasedate`,        date(max(`p`.`insertdate`)) `latestpurchasedate`,        datediff(date(min(`p`.`insertdate`)), date(`m`.`insertdate`)) `daysbetweenregandfirstpurchase`   `db`.`members` `m`        inner join `db`.`registration_steps` `r` on `m`.`memberid` = `r`.`memberid`         inner join `db`.`purchases` `p` on `m`.`memberid` = `p`.`memberid`  `m`.`insertdate` between '2013-07-01 00:00:00' , '2013-07-31 23:59:59'    , `r`.`completed` = 1 group `m`.`memberid` ;

it shows me want members missing record in table purchases.

here get:

| memberid  | regdate             | totaltransactions | firstpurchasedate   | latestpurchasedate  | daysbetweenregandfirstpurchase | | ==========|=====================|===================|=====================|=====================|================================| | 1         | 2013-08-01 18:18:16 | 3                 | 2013-08-02 18:18:16 | 2013-08-03 18:18:16 | 1                              | | 5         | 2013-08-05 18:18:16 | 1                 | 2013-08-07 18:18:16 | 2013-08-07 18:18:16 | 2                              |

but need is:

| memberid  | regdate             | totaltransactions | firstpurchasedate   | latestpurchasedate  | daysbetweenregandfirstpurchase | | ==========|=====================|===================|=====================|=====================|================================| | 1         | 2013-08-01 18:18:16 | 3                 | 2013-08-02 18:18:16 | 2013-08-03 18:18:16 | 1                              | | 2         | 2013-08-02 18:18:16 | 0                 | null                | null                | -1                             | | 3         | 2013-08-03 18:18:16 | 0                 | null                | null                | -1                             | | 5         | 2013-08-05 18:18:16 | 1                 | 2013-08-07 18:18:16 | 2013-08-07 18:18:16 | 2                              |

in order achieve this, tried change second inner join left join, left outer join , put conditions first inner join condition. however, wasn't able desired result. (must admit interupted few long running queries might have been correct(?) though (total count members in real scenario 20k).)

anyone?

thanks in advance!

to result member table need left join other table , add group by member table.

sample

from `db`.`members` `m`        left outer join `db`.`registration_steps` `r` on `m`.`memberid` = `r`.`memberid`         left outer join `db`.`purchases` `p` on `m`.`memberid` = `p`.`memberid`

left [ outer ]

specifies rows left table not meeting join condition included in result set, , output columns other table set null in addition rows returned inner join.


Comments

Post a Comment

Popular posts from this blog

java - Run a .jar on Heroku -

-
i trying run .jar file on heroku, , can't find instructions anywhere on how this. have read wants me compile uploading heroku. possible, , if is, how this? if helps, trying run minecraft craftbukkit server, , don't have pom.xml file yet. you didn't provide details i'll try give , people reaches question way it. first of all, create heroku account , create heroku application (assumed have done that). second, need generate application jar , upload heroku. so, open terminal , install heroku cli (assuming unix terminal): $ sudo npm install -g heroku-cli you need have npm , nodejs installed, can check link: https://www.digitalocean.com/community/tutorials/how-to-install-node-js-on-ubuntu-16-04 once done that, can use npm update node version acceptable heroku-cli: $ sudo npm install -g n $ sudo n 7.10.0 and run: $ sudo npm install -g heroku-cli in order deploy jars, need install deploy plugin: $ sudo heroku plugins:install heroku-cli-deploy ...
Read more

java - Jtable duplicate Rows -

-
Image
when add new rows in jtable duplicate rows many times this method update jtable public final void tableaucomande(){ ((defaulttablemodel)tabcommande.getmodel()).getdatavector().clear(); session s=hibernateutil.getsessionfactory().opensession(); criteria cr1= s.createcriteria(commande.class); list <commande> lcs=cr1.list() ; criteria cr2= s.createcriteria(lignecommande.class); list <lignecommande> lck=cr2.list() ; if(lcs.size()!=0 && lck.size()!=0) { (commande lc:lcs) { for(lignecommande lig:lck){ ((defaulttablemodel)tabcommande.getmodel()).addrow(new object[]{ lc.getid_cmd(),lc.getnumcmd(),lc.getdate_cmd(),lc.getclient().getnomprenom(), lig.getproduit().getlib_prd(),lig.getquantite(),lig.getproduit().getid_prd() }); } } // tabcommande.setrowselectioninterval(tabcommande.getrowcount()-1,tabcommande.getrowcount()-1); } } this code add add data rows displaye...
Read more

validation - How to pass paramaters like unix into windows batch file -

-
i need pass various parameters .cmd file unix format file.cmd -configuration=value -source=value -flag but, try this: startlocal @echo off cls setlocal set cmdline=%* set configuration= set source= set badargs= set validation= goto main :splitargs echo splitargs(%*) if "%*" neq "" ( /f "tokens=1,2,* delims== " %%i in ("%*") call :assignkeyvalue %%i %%j & call :splitargs %%k ) goto :eof :assignkeyvalue echo assignkeyvalue(%1, %2) if /i %1==-configuration ( set configuration=%2 ) else if /i %1==-source ( set source=%2 ) else ( rem append unrecognised [key,value] badargs echo unknown key %1 set badargs=%badargs%[%1, %2] ) goto :eof :validate echo validating set validation=fail if defined configuration ( echo -configuration ok if defined source ( echo -source ok if not defined badargs ( set validation=success ) ) ) goto :eof :main cl...
Read more