Join or IF Statement??

[gkeyes]

Using Sugar 5c with Zucker Reports 1.8g

I am working with the NOTES section and trying to figure out how to write the report code for this section.

In order to pull the information that the note applies to you have to link in some way from the NOTES Table using the parent_type and parent_ID fields.

Example: A NOTE is created and it references an ACCOUNT. The parent_type will be populated with the word ACCOUNT and the parent_id will be populated with the key for the matching account record.

In my case the NOTE could reference a/an ACCOUNT, LEAD or CONTACT.

My question is how should I write this to pull the proper information for First/last name, actual account name or the Lead Name.

I have looked at the Join Command and cannot get that to work, although I must admit the join confuses me.

Would IF statements work in some way? Could someone give an example of the best way to work this?

Again if I did not make it clear, pulling the fields from NOTES is not a problem, trying to pull from THREE other tables and not knowing which it might be is my problem, so I need to query in some way I guess.

[gkeyes]

Any Ideas from any one?

[mikesolomon]

an example of IF & JOIN

Hope it helps


select parent_type, `accounts`.`name`, if (parent_type = 'cases', 'CASE', 'NOT CASE')
from tasks
left join cases on tasks.parent_id = cases.id
left JOIN accounts ON (tasks.parent_id = accounts.id OR cases.account_id = accounts.id)

[gkeyes]

Thanks this looks like it might do it for me. I can modify this format to suit what I need to do.

Thanks.