SQL query returns 0 rows for multiple AND conditions

multiple row subquery in sql
multiple column subquery in (select clause)
operators used in single-row subquery
sql nested select
sql if query returns no results then
how to execute a sql query only if another sql query has no results
single-row subquery returns how many rows
sql execute query based on condition

I want to classify these 2 rows based on last 3 columns by AND/OR the following details using filters:

Now The table structure as I am using needs multiple JOIN conditions:

1.m_recipient sample data(m_recipient):

2.m_invites_recipient sample data(m_invites_recipient):

3.m_custom_field_values sample data(m_custom_field_values):

Now for example when I try to get the user whose value is rinnegan = yes and sharingan = no, the result is empty row(Answer should be first row. Whereas If I try to use rinnegan = yes or sharingan = no then I am getting both rows.

SQL query:-

SELECT DISTINCT m_recipient.id, m_recipient.sd_recipient_id, 
  m_recipient.campaign_id, m_recipient.user_id, m_recipient.userid, 
  m_recipient.first_name, m_recipient.last_name, m_recipient.email, 
  m_recipient.survey_link, m_recipient.unsubscribe 
FROM m_invites_recipient 
JOIN m_recipient ON m_recipient.id = m_invites_recipient.m_recipient_sd_id 
LEFT OUTER JOIN m_custom_field_values ON m_custom_field_values.related_to_id = m_recipient.id 
WHERE m_invites_recipient.m_invites_id = 6 
  AND m_recipient.unsubscribe = 0  
  AND ( m_custom_field_values.custom_field_name = "rinnegan" 
    AND m_custom_field_values.value = "yes" 
    AND m_invites_recipient.m_invites_id = 6 
    AND m_recipient.unsubscribe = 0 )  
  AND ( m_custom_field_values.custom_field_name = "sharingan" 
    AND m_custom_field_values.value = "no" 
    AND m_invites_recipient.m_invites_id = 6 
    AND m_recipient.unsubscribe = 0 ) 

As @ADyson said you can use multiple join like this.

SELECT DISTINCT m_recipient.id, 
m_recipient.sd_recipient_id, 
m_recipient.campaign_id, 
m_recipient.user_id, 
m_recipient.userid, 
m_recipient.first_name, 
m_recipient.last_name, 
m_recipient.email, 
m_recipient.survey_link, 
m_recipient.unsubscribe 
FROM   m_invites_recipient 
   JOIN m_recipient 
     ON m_recipient.id = m_invites_recipient.m_recipient_sd_id 
   JOIN m_custom_field_values AS a0 
     ON a0.related_to_id = m_recipient.id 
   JOIN m_custom_field_values AS a1 
     ON a1.related_to_id = m_recipient.id 
WHERE  m_invites_recipient.last_opened = 0 
   AND m_invites_recipient.last_clicked = 0 
   AND m_invites_recipient.m_invites_id = 6 
    OR ( a0.custom_field_name = "sharingan" 
         AND a0.value = "no" 
         AND m_invites_recipient.m_invites_id = 6 
         AND m_recipient.unsubscribe = 0 ) 
    OR ( a1.custom_field_name = "rinnegan" 
         AND a1.value = "yes" 
         AND m_invites_recipient.m_invites_id = 6 
         AND m_recipient.unsubscribe = 0 )

For example, to compare more than two rows you just need to add one more JOIN

mysql - SQL query returns 0 rows for multiple AND conditions, As @ADyson said you can use multiple join like this. SELECT DISTINCT m_recipient.id, m_recipient.sd_recipient_id, m_recipient.campaign_id,  Multiple row subquery returns one or more rows to the outer SQL statement. You may use the IN, ANY, or ALL operator in outer query to handle a subquery that returns multiple rows. Contents: Using IN operator with a Multiple Row Subquery. Using NOT IN operator with a Multiple Row Subquery.

Perhaps you want to match rows with either match all the first set of bracketed criteria, or match all the second set of bracketed criteria?

If so then you can simply do this:

( m_custom_field_values.custom_field_name = "rinnegan" 
    AND m_custom_field_values.value = "yes" 
    AND m_invites_recipient.m_invites_id = 6 
    AND m_recipient.unsubscribe = 0 )  
  OR                             #note the change from AND to OR here
( m_custom_field_values.custom_field_name = "sharingan" 
    AND m_custom_field_values.value = "no" 
    AND m_invites_recipient.m_invites_id = 6 
    AND m_recipient.unsubscribe = 0 )

Using the WHERE clause to filter data in SQL, The use of AND and OR to specify multiple conditions. Why SELECT * from baby_names WHERE 1 = 1 will return all rows from the baby_names table. The following queries are valid SQL but would return 0 results in the given data set,  In the preceding nested query, both the subquery and the enclosing statement operate on the same table. The subquery returns a single value: the maximum list price in the PRODUCT table. The outer query retrieves all rows from the PRODUCT table that have that list price.

Just for fun, here's an example of a valid query:

SELECT DISTINCT r.id
              , r.sd_recipient_id
              , r.campaign_id
              , r.user_id
              , r.userid
              , r.first_name
              , r.last_name
              , r.email
              , r.survey_link
              , r.unsubscribe 
           FROM m_invites_recipient i
           JOIN r 
             ON r.id = i.m_recipient_sd_id 
           JOIN m_custom_field_values v
             ON v.related_to_id = r.id 
          WHERE i.m_invites_id = 6 
            AND r.unsubscribe = 0  
            AND 
                ( v.custom_field_name = "rinnegan" 
              AND v.value = "yes" 
              AND i.m_invites_id = 6 
              AND r.unsubscribe = 0
                )
             OR 
                ( v.custom_field_name = "sharingan" 
              AND v.value = "no" 
              AND i.m_invites_id = 6 
              AND r.unsubscribe = 0 
                ); 

Single-row and multiple-row subqueries, Subqueries that can return only one or zero rows to the outer statement are called single-row subqueries. Single-row subqueries are subqueries used with a​  The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. It sets the number of rows or non NULL column values. COUNT() returns 0 if there were no matching rows. Syntax: COUNT(*) COUNT( [ALL|DISTINCT] expression ) The above syntax is the general SQL 2003 ANSI standard syntax.

The problem with your query is the logic in your WHERE condition. It can be rewritten as the following:

WHERE m_invites_recipient.m_invites_id = 6 
  AND m_recipient.unsubscribe = 0  
  AND ( m_custom_field_values.custom_field_name = "rinnegan" 
    AND m_custom_field_values.value = "yes")  
  AND ( m_custom_field_values.custom_field_name = "sharingan" 
    AND m_custom_field_values.value = "no")

Which obviously will return no rows, since no rows are equal to both "rinnegan" AND "sharingan".

EDIT:

The problem here is that you are trying to use data from multiple rows in your where condition, which can prove tricky. There may be a more elegant way to do this, but a possible solution to do this is to use sub-queries to find the 'answers' to the questions. Something like the following:

WITH 
rinneganQuery AS (
    SELECT value AS rinneganAnswer, related_to_id FROM m_custom_field_values 
    WHERE custom_field_name = 'rinnegan'
),
sharinganQuery AS (
    SELECT value AS sharinganAnswer, related_to_id FROM m_custom_field_values 
    WHERE custom_field_name = 'sharingan'
)

SELECT DISTINCT inv.id, inv.sd_recipient_id, 
  inv.campaign_id, inv.user_id, inv.userid, 
  inv.first_name, inv.last_name, inv.email, 
  inv.survey_link, inv.unsubscribe 
FROM m_invites_recipient inv
INNER JOIN m_recipient rec ON inv.m_recipient_sd_id = rec.id 
INNER JOIN rinneganQuery rq ON inv.m_recipient_sd_id = rq.related_to_id
INNER JOIN sharinganAnswer sq ON inv.m_recipient_sd_id = sq.related_to_id
WHERE inv.m_invites_id = 6 
  AND rec.unsubscribe = 0
  AND rq.rinneganAnswer = 'yes'
  AND sq.sharinganAnswer = 'no';

How to Execute a SQL Query Only if Another SQL Query has no , If that predicate returns no rows, they wanted to run another query using a different predicate. rows=0 loops=1) One-Time Filter: (NOT $1) InitPlan 2 (​returns $1) -> CTE In Oracle, we've even seen a situation where the combined query How to Calculate Multiple Aggregate Functions in a Single Query  SQL query returns 0 rows for multiple AND conditions. Ask Question The problem here is that you are trying to use data from multiple rows in your where condition

SQL COUNT function, The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. COUNT() returns 0 if there were no matching rows. along with COUNT to COUNT the number of rows w.r.t. some condition or all of the rows, Select COUNT(*) from multiple tables. Whether you're learning SQL for the first time or just need a refresher, read this article to learn when to use SELECT, JOIN, subselects, and UNION to access multiple tables with a single statement.

MySQL IF() function, expr_true, Returns when the condition is TRUE. a string when expr_true is In the following example the MySQL statement returns the third SELECT IF((​SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END),'true','false'); The following sql statement will display the number of publishers in a row for  This is not a condition you would generally ever use in a real-world SQL query, but it is valid: return all rows for which the value of 1 is equal to the value of 2. Well, that is never true, so that query will always return no rows no matter what database you call it on.

MySQL 8.0 Reference Manual :: 13.2.13 UPDATE Statement, For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. Each matching row is updated once,​  pandas boolean indexing multiple conditions. It is a standrad way to select the subset of data using the values in the dataframe and applying conditions on it. We are using the same multiple conditions here also to filter the rows from pur original dataframe with salary >= 100 and Football team starts with alphabet ‘S’ and Age is less than 60

Comments
  • Seems correct to me,that`s what OR does.
  • But for AND its not working @Mihai
  • Well naturally it doesn't work with AND. There's no way you can ever have a row where all of ( m_custom_field_values.custom_field_name = "rinnegan" AND m_custom_field_values.value = "yes" AND m_invites_recipient.m_invites_id = 6 AND m_recipient.unsubscribe = 0 ) is true AND all of ( m_custom_field_values.custom_field_name = "sharingan" AND m_custom_field_values.value = "no" AND m_invites_recipient.m_invites_id = 6 AND m_recipient.unsubscribe = 0 ) is true.It's impossible to match both sets of criteria, they're contradictory, it's simple logic
  • Even just the first field causes a problem - you can't have a row where custom_field_name = "rinnegan" AND custom_field_name = "sharingan". It's trivially obvious that the field can't have both values at the same time. It's not 100% clear what you want to achieve but I have written an answer which suggests one possibility. It doesn't help that you haven't given us any sample data from the actual tables.
  • based on your sample data, due to JOIN m_recipient ON m_recipient.id = m_invites_recipient.m_recipient_sd_id you should only ever get the first row from m_recipient returned anyway, regardless of the other clauses...are there more rows in m_invites_recipient which you haven't shown us? Apart from that, the basic problem is you're trying to look at data in more than one row simultaneously, which SQL is not really designed for. You perhaps need to join to m_custom_field_values twice, once for each possible custom field name value. Then you'll get all the data in one row instead of two.