Pardon my ignorance of MySQL but how do you hide columns in a report required for the select query?
For example, the following report lists the computers with Adobe Acrobat Pro 7 installed based on a software label. It uses the Machine ID (MID) and Software ID (ID) which end up as identical "behind the scenes" numbers both wasting space on the report output so I'd like those two columns hidden.

select MACHINE.NAME, MACHINE.SYSTEM_DESCRIPTION as DESCRIPTION,
MACHINE.IP, SUBSTRING(USER_LOGGED, LOCATE('\\\\',USER_LOGGED)+1) as USER,
MACHINE.ID as MID, SOFTWARE.ID from MACHINE,
MACHINE_SOFTWARE_JT, SOFTWARE,
SOFTWARE_LABEL_JT SWL, LABEL

where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and
MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and
SOFTWARE.ID=SWL.SOFTWARE_ID and
LABEL.ID=SWL.LABEL_ID and
LABEL.NAME='s-Ado Acrobat Pro 7 Win'
order by MACHINE.NAME
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Everything between select and from is the list of columns you want displayed. You do NOT have to list MACHINE.ID and SOFTWARE.ID as columns in order to use that data in the where clause. Just take those out and the query will still work.
Answered 05/20/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
Thanks airwolf. That worked. I also had to add the "group by MACHINE.ID" line to avoid duplicates. Here is the final version which I will duplicate for other software label reports:
select MACHINE.NAME as COMPUTER,
SUBSTRING(USER_LOGGED, LOCATE('\\\\',USER_LOGGED)+1) as USER,
MACHINE.SYSTEM_DESCRIPTION as DESCRIPTION, MACHINE.IP from MACHINE,
MACHINE_SOFTWARE_JT, SOFTWARE, SOFTWARE_LABEL_JT SWL, LABEL
where MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID and
MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID and
SOFTWARE.ID=SWL.SOFTWARE_ID and LABEL.ID=SWL.LABEL_ID and
LABEL.NAME='s-Ado Acrobat Pro 7 Win'
group by MACHINE.ID
order by MACHINE.NAME
Answered 05/20/2011 by: RichB
Third Degree Green Belt

Please log in to comment
Answer this question or Comment on this question for clarity