locked
Need Query to Combine Data on Separate Tables RRS feed

  • Question

  • User-718146471 posted

    Hello folks. I'm trying to hammer out this query and having some difficulty. I have four tables, one is a codex which refers to the RecordID of each table with data in it. I've called this one Vuln_Codex. The other three are Vuln_Cats (categories), Vuln_CWE, and Vuln_OWASP10. The data structure for the codex table is its master record ID and the Record ID of the other three that relate to each other. Below is the schema for these tables:

    Vuln_Codex
            - Codex_RecID INT ID PK
            - cat_id
            - cwe_id
            - owasp_id

    Vuln_Cats
            - Cat_RecID
            - 
    Cat_Name
            - Cat_Description

    Vuln_CWE
            - CWE_RecID
            - CWE_ID (from mitre.org - real world ID for Common Weakness Enumerator)
            - CWE_Name

    Vuln_OWASP10 (OWASP Top 10)
            - O_RecID
            - O_Name
            - O_Description
            - O_Year

    What I want to query is the following

    Codex_RecID Cat_Name Description CWE-ID CWE_Name OWASP Name OWASP DESCR

    When I had my query that worked, it only presented 10 records, I suspect because of the USING clause. I have 33 records on my Codex table because there are 33 categories.

    Here is the query which kind of baffles me because it was working, now it gives me an error:

    SELECT
    vuln_cats.Cat_id,
    vuln_cats.cat_Name,
    vuln_cwe.CWE-ID,
    vuln_cwe.CWE_Name,
    vuln_OWASP10.O_RecID,
    vuln_OWASP10.O_Name,
    vuln_OWASP10.O_Description
    FROM vuln_codex
    INNER JOIN
    vuln_cats USING (Cat_RecID)
    INNER JOIN
    vuln_cwe using (CWE_RecID)
    INNER JOIN
    vuln_OWASP10 using (O_RecID)

    Error Code: 1054. Unknown column 'Cat_RecID' in 'from clause' 0.000 sec

    Thursday, August 13, 2020 3:08 PM

Answers

  • User475983607 posted

    The USING clause requires the column names to be the same which they are no in your example schema.   That's why you are getting the unknown column error.

    Use ON and specify the joined columns rather than USING.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 13, 2020 3:27 PM

All replies

  • User475983607 posted

    The USING clause requires the column names to be the same which they are no in your example schema.   That's why you are getting the unknown column error.

    Use ON and specify the joined columns rather than USING.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, August 13, 2020 3:27 PM
  • User-718146471 posted

    Gotcha, I'll try that and circle back with you. Thanks!

    Thursday, August 13, 2020 4:28 PM
  • User-718146471 posted

    Ok, I am getting a result though not the one I want. The query below gives me a cartesian product. Can you help?

    SELECT vuln_codex.Codex_RecID,
    	vuln_cats.Cat_Name, vuln_cwe.CWE_ID, vuln_cwe.CWE_Name, 
    	vuln_OWASP10.O_Name, vuln_OWASP10.O_Description
    FROM vuln_cats, vuln_cwe, vuln_OWASP10, vuln_codex
    JOIN vuln_cats AS SCA ON 
    	SCA.Cat_RecID = vuln_codex.cat_id
    JOIN vuln_cwe AS SCW ON
    	SCW.CWE_RecID = vuln_codex.cwe_id
    JOIN vuln_OWASP10 AS SOW ON
    	SOW.O_RecID = vuln_codex.owasp_id;

    I should only have 33 rows, I end up with 359K rows.

    Thursday, August 13, 2020 4:45 PM
  • User-718146471 posted

    Never mind, I figured it out. Posting here to help others in the same situation.

    SELECT SCO.Codex_RecID,
    	SCA.Cat_Name, SCW.CWE_ID, SCW.CWE_Name, 
    	SOW.O_Name, SOW.O_Description
    FROM vuln_codex SCO
    JOIN vuln_cats SCA ON 
    	 SCO.cat_id = SCA.Cat_RecID
         JOIN vuln_cwe SCW ON
         SCO.cwe_id = SCW.CWE_RecID
         JOIN vuln_OWASP10 SOW ON
         SCO.owasp_id = SOW.O_RecID;

    Thursday, August 13, 2020 4:55 PM