I see it! Tito, you have to "pivot" those fields out of your "custom fields/custom field values" table(s). One of the best places to do that is into a temporary table so that you can join your pivoted rows into the rest of the report. I am assuming that the `eventum_custom_field` table has a field something like `issue_id` that associates a field with an issue. I am also assuming that there is only one custom field of any one type per issue.
CREATE TEMPORARY TABLE tmpCustFields SELECT cf.issue_id , MAX(if(fld_id=47,cfo.cfo_value,NULL)) as ProductName , MAX(if(fld_id=59,cfo.cfo_value,NULL)) as ProjectName , MAX(if(fld_id=4,cfo.cfo_value,NULL)) as ChangeRequester , MAX(if(fld_id=1,cfo.cfo_value,NULL)) as ChangeType , MAX(if(fld_id=2,cfo.cfo_value,NULL)) as ReqCompletionDate , MAX(if(fld_id=46,cfo.cfo_value,NULL)) as BSALead , MAX(if(fld_id=37,cfo.cfo_value,NULL)) as Developer FROM eventum_custom_fields cf LEFT JOIN eventum_custom_field_options cfo ON cf.cf_id = cfo.cfo_custom_field_id GROUP BY cf.issue_id; You will need to modify the column names in the ON clause to match the actual names of the columns that you need to relate an option to a field or an issue (whichever works). This query builds your middle columns into a table of their own. I hope that once you get them this far, JOINing this temp table to the rest of the tables you need to build your query will look pretty straight-forward. Look at the data to see what we did.... SELECT * from tmpCustFields limit 100; Hope that helps! Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Kraer, Joseph" <[EMAIL PROTECTED]> wrote on 12/06/2005 10:39:01 AM: > 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] >
