SELECT ASSET.NAME AS ASSET_NAME,R27.NAME AS FIELD_R27,R24.NAME AS FIELD_R24,R24_L10001.NAME AS R24_FIELD_10001 FROM ASSET_DATA_2  LEFT JOIN ASSET ON ASSET_DATA_2.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=2 LEFT JOIN ASSET_ASSOCIATION JR27 ON JR27.ASSOCIATED_ASSET_ID = ASSET.ID AND JR27.ASSET_FIELD_ID=27
                                                 LEFT JOIN ASSET R27 ON R27.ID = JR27.ASSET_ID
                                                 LEFT JOIN ASSET_DATA_5 RD27 ON RD27.ID=R27.ASSET_DATA_ID LEFT JOIN ASSET_ASSOCIATION JR24 ON JR24.ASSOCIATED_ASSET_ID = ASSET.ID AND JR24.ASSET_FIELD_ID=24
                                                 LEFT JOIN ASSET R24 ON R24.ID = JR24.ASSET_ID
                                                 LEFT JOIN ASSET_DATA_6 RD24 ON RD24.ID=R24.ASSET_DATA_ID LEFT JOIN LABEL R24_L10001 ON R24_L10001.ID = RD24.FIELD_10001   ORDER BY ASSET_NAME,FIELD_R27,FIELD_R24
       GROUP_CONCAT(DISTINCT A2.NAME SEPARATOR '\n') AS LICENSES,
       COUNT(DISTINCT J3.ASSOCIATED_ASSET_ID) AS APPROVED_COUNT,
       COUNT(DISTINCT MACHINE.ID) AS UNAPPROVED_COUNT,
       GROUP_CONCAT(DISTINCT MACHINE.NAME SEPARATOR '\n') AS MACHINES
       from ASSET 
       LEFT JOIN ASSET_ASSOCIATION J21 ON J21.ASSET_ID = ASSET.ID AND J21.ASSET_FIELD_ID=21
       LEFT JOIN ASSET A21 ON A21.ID = J21.ASSOCIATED_ASSET_ID
       LEFT JOIN ASSET_ASSOCIATION J2 ON J2.ASSOCIATED_ASSET_ID = ASSET.ID AND J2.ASSET_FIELD_ID=2
       LEFT JOIN ASSET A2 ON A2.ID = J2.ASSET_ID
       LEFT JOIN ASSET_ASSOCIATION J3 ON
          J3.ASSET_ID = A2.ID
       AND J3.ASSET_FIELD_ID = 3
       LEFT JOIN MACHINE_SOFTWARE_JT ON (SOFTWARE_ID = ASSET.MAPPED_ID OR SOFTWARE_ID IN
         (SELECT SOFTWARE_ID FROM ASSET_DATA_6 JOIN SOFTWARE_LABEL_JT
                             ON LABEL_ID = ASSET_DATA_6.FIELD_10001
                    WHERE ASSET_DATA_6.ID = ASSET.ASSET_DATA_ID))
       LEFT JOIN MACHINE on MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID
where (ASSET.ASSET_TYPE_ID = 6) 
  AND NOT EXISTS (SELECT 1 FROM ASSET C, ASSET_ASSOCIATION J3, ASSET_ASSOCIATION J2
                         WHERE C.ASSET_TYPE_ID=5 AND C.MAPPED_ID=MACHINE.ID
                           AND J3.ASSET_ID=J2.ASSET_ID
                           AND J3.ASSOCIATED_ASSET_ID = C.ID
                           AND J3.ASSET_FIELD_ID = 3
                           AND J2.ASSOCIATED_ASSET_ID = ASSET.ID
                           AND J2.ASSET_FIELD_ID = 2)
GROUP BY ASSET.ID
HAVING LICENSES IS NOT NULL
order by ASSET.NAME

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

1

Whats the error your getting?

The trouble with custom asset reports is that each Kbox database is different.

That makes it very difficult for someone else to troubleshoot.

Answered 04/30/2012 by: dchristian
Red Belt

  • This is the error:

    mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP_CONCAT(DISTINCT A2.NAME SEPARATOR '\n') AS LICENSES,
    COUNT(DISTINC' at line 6] in EXECUTE(

    I'm trying to combine two reports that give similar info into one that gives me what I need.

    D
  • It looks like your missing the last "T" in DISTINCT
Please log in to comment

Answers

0

You have a lot of things out of order, for instance, this section:

GROUP_CONCAT(DISTINCT A2.NAME SEPARATOR '\n') AS LICENSES,
       COUNT(DISTINCT J3.ASSOCIATED_ASSET_ID) AS APPROVED_COUNT,
       COUNT(DISTINCT MACHINE.ID) AS UNAPPROVED_COUNT,
       GROUP_CONCAT(DISTINCT MACHINE.NAME SEPARATOR '\n') AS MACHINES
       

Needs to be above the join statements, or rather the join statements need to come after the FROM ASSET. There's also another FROM ASSET_DATA_2 line further up in the code. It looks like you combined a few reports into one, but things aren't put together correctly. 

The general syntax for a query is

select fields (a list of comma separated column names) 

from table

joins to other tables

where certain things are true

ordered by fields


Answered 05/01/2012 by: chucksteel
Red Belt

  • I am very rusty on SQL. I haven't touched it in over 20 years, so I appreciate any hints, help or suggestions. I'll see what I can do to get it in the correct order and repost the code if I still need assistance. Thanks.
Please log in to comment
Answer this question or Comment on this question for clarity