phpmyadmin Question posted in
The official documentation can be found here.

SQL- Joining tables and checking if something exists not working/incorrect syntax – Phpmyadmin

I am trying to write an sql statement in phpmyadmin.

I have three tables – one tables has all of the data from my posts with a unique ID of wp_posts.ID.

The other table is a tags table that has a list of tags that can be added to posts. Each tag has a unique tag id called term_taxonomy_id.

The last table is a relationship table that only creates a record if a tag has been added to a post. This has a unique ID called Object_ID. This Object_ID will be equal to the ID in the wp_posts table.

What I am trying to achieve is to retrieve the posts that do not have a tag associated with them which should mean that they do not have a record in the relationship table.

Here is the SQL I have tried: 

SELECT *
  FROM wp_posts
  JOIN wp_term_relationships
    ON wp_posts.ID = wp_term_relationships.object_id
 WHERE NOT EXISTS( 
SELECT * FROM wp_term_relationships 
      )

I believe that this syntax should work for what im trying to do but I am getting 0 results even though there are posts that do not have tags associated with them.

Can anyone help with where I am going wrong?

Tags:

2

Answers


  1. 0

    You can use LEFT JOIN/WHERE or NOT EXISTS. For example:

    SELECT p.*
FROM wp_posts p LEFT JOIN
     wp_term_relationships tr
     ON p.ID = tr.object_id LEFT JOIN
     tags t
     ON t.id = tr.term_taxonomy_id AND
        t.name = ?
WHERE t.id IS NULL;
    Login or Signup to reply.
  2. 0

    You can do this by either using a left join or a not exists (left join should be faster):

    SELECT *
FROM wp_posts
LEFT JOIN wp_term_relationships
   ON wp_posts.ID = wp_term_relationships.object_id
WHERE wp_term_relationships.object_id is null

    OR

    SELECT *
FROM wp_posts wp
WHERE not exists(select * from wp_term_relationships where object_id = wp.ID)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search