[SOLVED] PHP database results: Need to append rather than show matches !?!

[webmaster1]

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?

[sasa]

LEFT JOIN mysql command

[webmaster1]

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

[printf]

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;

 

 

 

[webmaster1]

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.

[webmaster1]

I'm trying the MAX() function but not sure where to fit it in. I have a timestamp on each of the tables btw.

[webmaster1]

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

[printf]

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!

[sasa]

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

[webmaster1]

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. 

[webmaster1]

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.

[sasa]

order database and group just use 1st value

[webmaster1]

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.