error:SQL syntax; check the manual that corresponds to your MariaDB server version for the right syn-CodePudding

SELECT object_id 
FROM wp_term_relationships 
left JOIN (SELECT COUNT(column_11) 
           FROM wp_tablesome_table_4695
          ) ON wp_term_relationships.object_id = wp_tablesome_table_4695.column_2 
WHERE term_taxonomy_id = '12'

error:SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ON wp_term_relationships.object_id = wp_tablesome_table_4695.column_2 WHERE t...' at line 1

wp_term_relationships
    | object_id| 
    |:----     |
    |  1       | 
    |  2       |
    |  3       |



wp_tablesome_table_4695
        | column_2 | column_11
        |:----     |
        |  1       | address1
        |  2       | address2
        |  3       | address3
result
        | cloumn_11 | count
        |:----      |
        |  location1| 3
        |  location2| 22
        |  location3| 10

The wp_term_relationships object_id and wp_tablesome_table_4695 column_2 have the same value and the term_taxonomy_id = '12' is the category and the column_11 is the address please help me count the total id that has the same address thanks in advance

CodePudding user response：

The problem start that a subquery needs a alias in my case t2 . i alsol added one for the first table, which makes it easier to read.

Next you subquery only returns one value, but to join the subquery needs also to return column2 so that you can use it in the ON clause.

Last i don't know which counts you actaully need, but i am guessing the for every objectid.

so oyur query must look like:

SELECT 
    t1.object_id,t2.counts
FROM
    wp_term_relationships t1
        LEFT JOIN
    (SELECT 
        column_2, COUNT(column_11) counts
    FROM
        wp_tablesome_table_4695
    GROUP BY column_2) t2 ON t1.object_id = t2.column_2
WHERE
    term_taxonomy_id = '12'

For column_11 you must change all column2 to column_11

SELECT 
    t2.column_11, t2.counts
FROM
    wp_term_relationships t1
        LEFT JOIN
    (SELECT 
         column_11, COUNT(column_11) counts
    FROM
        wp_tablesome_table_4695
    GROUP BY column_11) t2 ON t1.object_id = t2.column_2
WHERE
    t1.term_taxonomy_id = '12'