CODEWITHSUNDEEP
mysqljoin
m.b
m.b

Reputation: 15

Mysql rows from table A with ID from query where ID matches one condition true in table B


    table wp_posts
    +-----+----------+
    | ID  |post_type |
    +-----+----------+
    |  1  |        A |
    |  2  |        A |
    |  3  |        A | 
    |  3  |        B | 
    |  3  |        C | 
    +-----+----------+



    table wp_term_relationships (object_id = wp_posts.ID)
    +-----------+------------------+
    | object_id | term_taxonomy_id |
    +-----------+------------------+
    |  1        |                1 |
    |  2        |                3 |
    |  2        |                2 |
    |  1        |                2 |
    |  3        |                1 | 
    +-----------+-------------------+



    table wp_esp_datetime (EVT_ID = wp_posts.ID)
    +-----------+---------------------+
    | EVT_ID    | DTT_EVT_end         |
    +-----------+---------------------+
    |  1        | 2015-12-01 16:00:00 |
    |  2        | 2015-12-01 16:00:00 |
    |  3        | 2015-12-01 16:00:00 |
    +-----------+---------------------+

Now what I want to do is: query all from wp_posts with left joined datetime from wp_esp_datetime, but only those who have no entry in wp_term_relationships with term_taxonomy_id = 3. My problem is, that the wp_post.ID can be multiple times in wp_term_relationships.object_id

My current query looks like this:

SELECT * FROM wp_posts 
INNER JOIN wp_term_relationships on wp_posts.ID = wp_term_relationships.object_id 
LEFT JOIN wp_esp_datetime on wp_posts.ID = wp_esp_datetime.EVT_ID
WHERE wp_posts.post_type = 'A' GROUP BY wp_posts.ID

So the question is:

How can i exclude the row from wp_posts which has any entry with term_taxonomy_id = 3 in wp_term_relationships, even if there is a match with another term_taxonomy (not 3)?

Upvotes: 0

Views: 161

Answers (4)

Strawberry
Strawberry

Reputation: 33935

I can't see what part of the problem the following fails to address...

SELECT p.*
     , d.*
  FROM wp_posts p
  LEFT 
  JOIN wp_esp_datetime d 
    ON d.evt_id = p.id
  LEFT 
  JOIN wp_term_relationships r
    ON r.object_id = p.id 
   AND r.term_taxonomy_id = 3
 WHERE term_taxonomy_id IS NULL;

This will produce the following result...

 +----+-----------+--------+---------------------+
 | ID | post_type | EVT_ID | DTT_EVT_end         |
 +----+-----------+--------+---------------------+
 |  1 | A         |      1 | 2015-12-01 16:00:00 |
 |  3 | A         |      3 | 2015-12-01 16:00:00 |
 |  3 | B         |      3 | 2015-12-01 16:00:00 |
 |  3 | C         |      3 | 2015-12-01 16:00:00 |
 +----+-----------+--------+---------------------+

It would seem nonsensical to apply a GROUP BY clause to this result set.

Upvotes: 0

trincot
trincot

Reputation: 350087

Use not exists and remove the join with wp_term_relationships:

SELECT      p.ID, ed.DTT_EVT_end 
FROM        wp_posts p
LEFT JOIN   wp_esp_datetime ed on p.ID = ed.EVT_ID 
WHERE       p.post_type = 'A' 
AND         NOT EXISTS (
                SELECT  1 
                FROM    wp_term_relationships
                WHERE   term_taxonomy_id = 3
                AND     p.ID = object_id)
ORDER BY    p.ID, ed.DTT_EVT_end;

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

Add an (not) exists clause

SELECT * FROM wp_posts p
INNER JOIN wp_term_relationships tr on p.ID = tr.object_id 
LEFT JOIN wp_esp_datetime ed on p.ID = ed.EVT_ID 
WHERE p.post_type = 'A' 
and not exists (select null 
                from wp_term_relationships
                where term_taxonomy_id = 3
                and p.ID = object_id)
GROUP BY p.ID

Upvotes: 1

Mateo Barahona
Mateo Barahona

Reputation: 1391

Try adding a subquery ?

 AND wp_posts.ID NOT IN
(SELECT wtr2.object_id FROM wp_term_relationships wtr2 WHERE wtr2.term_taxonomy_id = 3)

Your query edited :

SELECT *
FROM wp_posts p
INNER JOIN wp_term_relationships wtr on p.ID = wtr.object_id
LEFT JOIN wp_esp_datetime wed on p.ID = wed.EVT_ID
WHERE p.post_type = 'A'
AND p.ID NOT IN (SELECT wtr2.object_id FROM wp_term_relationships wtr2 WHERE wtr2.term_taxonomy_id = 3)
GROUP BY p.ID

Upvotes: 0

Related Questions