Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to list all records in a mysql master table and last record in the child table that is related to the master. Here is my code

$strsql="SELECT contact.id, contact.title, contact.init, contact.first_name, contact.last_name, contact.city, contact.home_phone, contact.mobile_phone, contact.e_mail,  status.status AS status, fundsource.fundsource AS fundsource, category.category AS category,contact.score, ROUND(value/1000000,1) AS value_M, jstate.jstate AS to_do, journal.l_date AS last_activity, journal.note AS journal FROM contact

LEFT JOIN journal ON journal.con_id=contact.id 
LEFT JOIN jstate ON jstate.jstate_code=journal.jstate_code
LEFT JOIN category ON category.cat_code=contact.cat_code
LEFT JOIN status ON status.st_code=contact.st_code
LEFT JOIN fundsource ON fundsource.fundsource_code=contact.fundsource_code 

WHERE journal.l_date=(SELECT MAX(journal.l_date) from journal WHERE journal.con_id = contact.id) GROUP BY contact.id " ;

But it outputs only related records which matching the PK and FK. Not displays all records in the master. Can any one help me? Pls.

share|improve this question

1 Answer 1

Your WHERE-condition is reducing the output. If you need all the records from the contact table, please try something like this:

$strsql="SELECT contact.id, contact.title, contact.init, contact.first_name, contact.last_name, contact.city, contact.home_phone, contact.mobile_phone, contact.e_mail,  status.status AS status, fundsource.fundsource AS fundsource, category.category AS category,contact.score, ROUND(value/1000000,1) AS value_M, jstate.jstate AS to_do, journal.l_date AS last_activity, journal.note AS journal FROM contact

LEFT JOIN journal ON journal.con_id=contact.id AND journal.l_date=(SELECT MAX(journal.l_date) from journal WHERE journal.con_id = contact.id)
LEFT JOIN jstate ON jstate.jstate_code=journal.jstate_code
LEFT JOIN category ON category.cat_code=contact.cat_code
LEFT JOIN status ON status.st_code=contact.st_code
LEFT JOIN fundsource ON fundsource.fundsource_code=contact.fundsource_code 

WHERE contact.scode=$user->scode and contact.dcode=$user->dcode AND contact.st_code !=17 AND contact.st_code !=18
GROUP BY contact.id " ;
share|improve this answer
    
Yes. That is correct. Thank you very much. But I have another argument to be added to the WHERE condition to filter userwise records and no need records. Arguments - contact.scode= '$user->scode' and contact.dcode= '$user->dcode' AND contact.st_code !=17 AND contact.st_code !=18. Can you help for that. Pls. –  Manoj Apr 28 at 5:08
    
The information you provided is not enough. What is the meaning of your query? Please add the example data and the desired output. –  namaste Apr 28 at 5:13
    
Yes. I want to filter records also user wise and not displays status that is st_code 17 and 18. Here is my previous condition WHERE journal.l_date=(SELECT MAX(journal.l_date) from journal WHERE journal.con_id = contact.id) AND contact.scode= '$user->scode' and contact.dcode= '$user->dcode' AND contact.st_code !=17 AND contact.st_code !=18 GROUP BY contact.id –  Manoj Apr 28 at 5:17
    
Just try adding the WHERE contact.scode='$user->scode' AND contact.dcode='$user->dcode' AND contact.st_code !=17 AND contact.st_code !=18' to the query before the GROUP BY clause. Is this wrong result? –  namaste Apr 28 at 5:20
    
contact.scode= '$user->scode' and contact.dcode= '$user->dcode' AND contact.st_code !=17 AND contact.st_code !=18 condition is working correctly. Where to add this? pls –  Manoj Apr 28 at 5:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.