How to select rows with LEFT JOIN if specified value exists in joined table?

Question

I not imagine how to build query. I need to select rows from table CONTENT which hasn't column tmplvarid=8 in left joined TEMPLATE VARIABLES column

SELECT con.id, con.alias, tv.tmplvarid FROM modx_site_content as con LEFT JOIN modx_site_tmplvar_contentvalues as tv ON con.id=tv.contentid WHERE con.deleted=0 AND (con.content IS NULL OR con.content='') AND con.template=5 AND tv.tmplvarid!=8/*not contains 8*/

Explanation: https://i.stack.imgur.com/gdgha.png

Possible duplicate of [MySQL Join Where Not Exists](https://stackoverflow.com/questions/750343/mysql-join-where-not-exists) — mickmackusa, Jan 18 '19 at 23:43

score 1 · Answer 1 · answered Jun 06 '17 at 14:49

Simple: Don't do an INNER join, do an OUTER JOIN instead:

SELECT con.id, con.alias, tv.tmplvarid FROM modx_site_content as con 
    LEFT OUTER JOIN modx_site_tmplvar_contentvalues as tv ON con.id=tv.contentid 
       AND tv.templvarid = 8 
         WHERE con.deleted=0 AND (con.content IS NULL OR con.content='') 
     AND con.template=5 AND tv.contentid IS NULL

So you join for templvarid = 8 in an OUTER JOIN - and then select (in where) only the rows that have NULL in tv. contentid (that's those where not templvarid 8 could be found for join)

https://stackoverflow.com/questions/406294/left-join-vs-left-outer-join-in-sql-server#comment62672922_406294 — mickmackusa, Jan 18 '19 at 23:41

How to select rows with LEFT JOIN if specified value exists in joined table?

1 Answers1