2

I have searched for all answers, but nothing works. Somehow this query gives me the ambiguity error or 'ID':

SELECT wpp.ID AS theid, s.ID AS supplierid, post_title, wp_terms.name AS category 
FROM wp_posts AS wpp
LEFT JOIN wp_term_relationships ON wpp.ID = object_id
LEFT JOIN wp_terms ON term_taxonomy_id = term_id
LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID
WHERE post_type = 'post'
AND (post_title LIKE '%bag%'
OR wp_terms.name LIKE '%bag%'
OR post_content LIKE '%bag%')
GROUP BY ID

the problem lies when I added this line:

LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID

But I don't see what I'm doing wrong... Any hints?

4

GROUP BY ID. That's where the problem is. MySQL can't guess which table this column ID belongs to. There is an ID column in both the added wp_teleapo_supplier table and in one of the 3 others (I guess the wp_posts one).

All your column references should be prefixed with the table name (or alias): (object_id, term_id, post_type, post_title, post_content, ID). The only reason that these other references did not produce this error is because you were lucky and they only appeared in a single table, from the 4 tables in the FROM clause.

(some of the prefixes may be wrong. But if they are, you'll get an error and you can correct them:)

SELECT wpp.ID AS theid, s.ID AS supplierid, wpp.post_title, t.name AS category 
FROM wp_posts AS wpp
  LEFT JOIN wp_term_relationships AS r ON wpp.ID = r.object_id
  LEFT JOIN wp_terms AS t ON term_taxonomy_id = t.term_id
  LEFT JOIN wp_teleapo_supplier AS s ON wpp.post_author = s.ID
WHERE wpp.post_type = 'post'
  AND (wpp.post_title LIKE '%bag%'
    OR t.name LIKE '%bag%'
    OR wpp.post_content LIKE '%bag%')
GROUP BY wpp.ID ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.