Gleb, What I need to have is a total of 16 columns: issue ID, priority, assigned (to), project name, category, status, last update date, summary, product name, project name (not the same as before), change requester, change type, requested completion date, lead, developer, impacted dept.
I have no problems generating the first eight or the last columns. The seven in between are the issue as they are not columns per se. They are cells in different tables. I need to extract the contents of certain cells, based on certain IDs, to be used as the column headings in the output to my query. Then, I need to look into other tables to fill those columns. This is what I need help with: how do I generate those columns? I guess they could be generated separately and then I could put both of my outputs together. Unfortunately, time is running out. TIA, Joseph "Tito" Kraer Business Systems Analyst Taylor, Bean & Whitaker Mortgage Corp -----Original Message----- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 6:52 AM To: mysql@lists.mysql.com Subject: Re: Newbie Question: listing open issues regardless of project Hello. > Please, I don't need comments telling me that I'm missing the semicolon > or that comments such as "doesn't work" are worthless. I know there's > something inherently wrong with this query (obviously . . . since it > doesn't give me the results that I am looking for). Does anyone have a > constructive comment as to how to do this? Please, could you answer what are you going to get in the last columns. You've said that you had problems with the last seven columns, but from the logic of your query I see that you just want a column, which changes its value depending on the value in other field. Am I correct? Please provide a sample output (what you want to see in the results) in case I'm wrong. Now I'm not talking about syntax, but rather about sense of the query. Kraer, Joseph wrote: > I am sending this message to both Eventum and MySQL support lists. > > I am trying to write a select statement in the MySQL Query Browser (v. > 1.1.10) on my Eventum (v. 1.4) database; MySQL version is 4.0.21 (PHP is > 4.3.10). My goal is to list certain data from all open issues, across > projects, as well as listing some issue details contained in custom > fields. I don't have a problem getting the data for the first eight > columns of my query. My problem lies with listing the next seven > columns, which come from custom fields. I thought of using a PHP script > but this is a one-time deal and my knowledge of PHP is as poor as that > of MySQL. > > As Eventum users may know, headings for custom fields are extracted from > cells in the eventum_custom_field_option table. I thought that IF > statements would do the job, but I get a syntax error (1064). > Obviously, they are not the way to go. Nevertheless, here's the > complete query so you can get an idea of where I want to go: > > SELECT DISTINCT > eventum_issue.iss_id AS "Issue ID", > eventum_project_priority.pri_title AS "Priority", > eventum_user.usr_full_name AS "Assigned", > eventum_project.prj_title AS "Project Name", > eventum_project_category.prc_title AS "Category", > eventum_status.sta_title AS "Status", > eventum_issue.iss_updated_date AS "Last Update Date", > eventum_issue.iss_summary AS "Summary", > > IF eventum_custom_field.fld_id =3D "47" > THEN eventum_custom_field_option.cfo_value AS "Product > Name" > ELSE IF eventum_custom_field.fld_id =3D "59" > THEN eventum_custom_field_option.cfo_value AS "Project > Name" > ELSE IF eventum_custom_field.fld_id =3D "4" > THEN eventum_custom_field_option.cfo_value AS "Change > Requester" > ELSE IF eventum_custom_field.fld_id =3D "1" > THEN eventum_custom_field_option.cfo_value AS "Change > Type" > ELSE IF eventum_custom_field.fld_id =3D "2" > THEN eventum_custom_field_option.cfo_value AS "Requested > Completion Date" > ELSE IF eventum_custom_field.fld_id =3D "46" > THEN eventum_custom_field_option.cfo_value AS "BSA Lead" > ELSE IF eventum_custom_field.fld_id =3D "37" > THEN eventum_custom_field_option.cfo_value AS > "Developer" > > FROM eventum_issue, eventum_custom_field, eventum_custom_field_option > INNER JOIN > eventum_project_priority, > eventum_issue_user, > eventum_user, > eventum_project, > eventum_project_category, > eventum_status > eventum_issue_custom_field > WHERE eventum_issue.iss_pri_id =3D eventum_project_priority.pri_id > AND eventum_issue.iss_id =3D eventum_issue_user.isu_iss_id > AND eventum_issue_user.isu_usr_id =3D eventum_user.usr_id > AND eventum_issue.iss_prj_id =3D eventum_project.prj_id > AND eventum_issue.iss_prc_id =3D eventum_project_category.prc_id > AND eventum_issue.iss_sta_id =3D eventum_status.sta_id > AND (eventum_issue.iss_closed_date IS NULL > OR (eventum_issue.iss_closed_date IS NOT NULL > AND (eventum_issue.iss_sta_id !=3D "5" > OR eventum_issue.iss_sta_id !=3D "6" > OR eventum_issue.iss_sta_id !=3D "9"))) > ORDER BY eventum_issue.iss_id > > Please, I don't need comments telling me that I'm missing the semicolon > or that comments such as "doesn't work" are worthless. I know there's > something inherently wrong with this query (obviously . . . since it > doesn't give me the results that I am looking for). Does anyone have a > constructive comment as to how to do this? > > Thank you very much in advance, > > Joseph "Tito" Kraer > Business Systems Analyst > Taylor, Bean & Whitaker Mortgage Corp > > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
