webmaster1 Posted February 8, 2009 Share Posted February 8, 2009 SELECT masterdata.masterdataid, masterdata.companyname, masterdata.contactname, resultsnotations.prospectrating, masterdata.lastactivity FROM masterdata, resultsnotations where masterdata.masterdataid = resultsnotations.masterdataid Hi All, I'm having a minor issue in displaying my database results (please don't move to the mysql help forum). Lets say I have 100 records in the 'masterdata' table. When I run the above query it only shows what matches the 'resultsnotations' table. Instead I want to show all of the records from the 'masterdata' table and simply append the required field from the 'resultsnotations' table when there is an input. Does anyone know how to go about this? Quote Link to comment Share on other sites More sharing options...
sasa Posted February 8, 2009 Share Posted February 8, 2009 LEFT JOIN mysql command Quote Link to comment Share on other sites More sharing options...
webmaster1 Posted February 8, 2009 Author Share Posted February 8, 2009 Very handy sql function. Thanks! SELECT masterdata.masterdataid, masterdata.companyname, masterdata.contactname, resultsnotations.prospectrating, masterdata.lastactivity FROM masterdata LEFT JOIN resultsnotations ON masterdata.masterdataid = resultsnotations.masterdataid ORDER BY masterdata.masterdataid Quote Link to comment Share on other sites More sharing options...
printf Posted February 8, 2009 Share Posted February 8, 2009 SELECT tb1.masterdataid, tb1.companyname, tb1.contactname, tb1.lastactivity, tb2.prospectrating FROM masterdata AS tb1 LEFT OUTER JOIN resultsnotations AS tb2 ON(tb1.masterdataid = tb2.masterdataid) ORDER BY tb1.masterdataid; Note... If * prospectrating * doesn't exist, * prospectrating * it will be set to NULL. If you don't want it to return a NULL value then surround it with a IF() statement and set the value you want to return if it does return NULL! Example... SELECT tb1.masterdataid, tb1.companyname, tb1.contactname, tb1.lastactivity, IF(tb2.prospectrating IS NULL, 'no value', tb2.prospectrating) AS prospectrating FROM masterdata AS tb1 LEFT OUTER JOIN resultsnotations AS tb2 ON(tb1.masterdataid = tb2.masterdataid) ORDER BY tb1.masterdataid; Quote Link to comment Share on other sites More sharing options...
webmaster1 Posted February 8, 2009 Author Share Posted February 8, 2009 Thanks printf. The 'if' statement for the null is great! I've hit one snag though. What if the second table ('resultsnotations') contains more than one record with the same universal id ('masterdataid)? I've tried it and it ends up showing each record though I only want it to show the record with the latest timestamp. Sorry to persist but I completley overlooked this. Quote Link to comment Share on other sites More sharing options...
webmaster1 Posted February 8, 2009 Author Share Posted February 8, 2009 I'm trying the MAX() function but not sure where to fit it in. I have a timestamp on each of the tables btw. Quote Link to comment Share on other sites More sharing options...
webmaster1 Posted February 8, 2009 Author Share Posted February 8, 2009 I'm trying to combine the following two queries... ??? SELECT masterdata.masterdataid, masterdata.companyname, masterdata.contactname, IF( resultsnotations.prospectrating IS NULL , 'no value', resultsnotations.prospectrating ) AS prospectrating, masterdata.lastactivity FROM masterdata LEFT JOIN resultsnotations ON masterdata.masterdataid = resultsnotations.masterdataid ORDER BY masterdata.masterdataid SELECT resultsnotations.masterdataid, MAX( resultsnotations.lastactivity ) FROM resultsnotations GROUP BY resultsnotations.masterdataid Quote Link to comment Share on other sites More sharing options...
printf Posted February 8, 2009 Share Posted February 8, 2009 Sorry I was watching a movie... Here you go... SELECT tb1.masterdataid, tb1.companyname, tb1.contactname, tb1.lastactivity, IF(tb2.prospectrating IS NULL, '', tb2.prospectrating) AS prospectrating, IF(MAX(tb2.lastactivity) IS NULL, '', MAX(tb2.lastactivity)) AS stamp FROM masterdata AS tb1 LEFT OUTER JOIN resultsnotations AS tb2 ON(tb1.masterdataid = tb2.masterdataid) GROUP BY tb2.prospectrating ORDER BY tb2.lastactivity DESC; Notes... change... ORDER BY tb2.lastactivity DESC; to this... ORDER BY tb1.masterdataid; if you want the order by master id instead of last activity! Quote Link to comment Share on other sites More sharing options...
sasa Posted February 8, 2009 Share Posted February 8, 2009 SELECT * FROM (SELECT masterdata.masterdataid, masterdata.companyname, masterdata.contactname, IF( resultsnotations.prospectrating IS NULL , 'no value', resultsnotations.prospectrating ) AS prospectrating, masterdata.lastactivity FROM masterdata LEFT JOIN resultsnotations ON masterdata.masterdataid = resultsnotations.masterdataid ORDER BY masterdata.masterdataid, resultsnotations.lastactivity DESC) AS a GROUP BY masterdataid Quote Link to comment Share on other sites More sharing options...
webmaster1 Posted February 8, 2009 Author Share Posted February 8, 2009 Thanks for the help guys... printf: For some odd reason your query only returns three rows. sasa: That works perfectly though I made one minor tweak... In the select part I changed masterdata.lastactivity to resultsnotations.activity (I probably didn't explain properly which timestamp I wanted use) Thanks so much for the assistance, or spoonfeeding rather. Quote Link to comment Share on other sites More sharing options...
webmaster1 Posted February 8, 2009 Author Share Posted February 8, 2009 sasa: I'm presuming that the grouping of the sub-query is what selects the highest timestamp value i.e. The highest value is appended (or maybe its the first since you ordered it in desc)? Not critical as the problem is solved but just wondering. Quote Link to comment Share on other sites More sharing options...
sasa Posted February 8, 2009 Share Posted February 8, 2009 order database and group just use 1st value Quote Link to comment Share on other sites More sharing options...
webmaster1 Posted February 8, 2009 Author Share Posted February 8, 2009 In the select part I changed masterdata.lastactivity to resultsnotations.activity SCRATCH THAT, YOU WERE SPOT ON. THE 'MASTERDATA' TIMESTAMP WILL ALWAYS REFLECT THE LATEST RECORD ACTIVITY. 1st value, eh? Understood and thanks. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.