MySQL "Not In" Query

MySQL NOT IN query

To use IN, you must have a set, use this syntax instead:

SELECT * FROM Table1 WHERE Table1.principal NOT IN (SELECT principal FROM table2)

Mysql NOT IN clause query does't return what I want

As others have said, you have many records for each member, and when at least one of the records matches your not in condition, it will appear in the result set.

One approach is to find all the members which do have the offending record, and then return the complement set:

SELECT 
m.idMembre
FROM
membres_has_domaines m
WHERE
m.idMembre NOT IN (
SELECT idMembre
FROM membres_has_domaines
WHERE idDomaine = 40
);

MySQL NOT IN query not working

Are there any NULLs in taxon_name_element.parent_id?

The query...

select taxon_id 
from taxon_name_element
where taxon_id not in (
select parent_id
from taxon_name_element
)

...is equivalent to...

select taxon_id 
from taxon_name_element
where
taxon_id <> parent_id_1
AND taxon_id <> parent_id_2
...
AND taxon_id <> parent_id_N

...where parent_id_X are actual values that are currently in the parent_id column. If even one of them is NULL, the corresponding taxon_id <> parent_id_X expressions will "collapse" into NULL, dragging the whole WHERE expression with it.

Filter-out NULLs to get what you want:

select taxon_id 
from taxon_name_element
where taxon_id not in (
select parent_id
from taxon_name_element
where parent_id is not null
)

PHP MySQL SELECT WHERE NOT IN

There are two mistake I am able to find in your query

  1. use column name instead of *

When you are using sub query and wants to match with a column then you have to write exact column name to which you want to compare otherwise it will give you an error.


  1. you have not properly ended brackets.

Close brackets properly.

As per comment, You have also one more issue.


  1. change $class to .$class

Try Below one.

  $g_query = $conn->query('SELECT * FROM memberdetails WHERE mem_id NOT IN (SELECT mem_id FROM memberdetails WHERE mem_id in (select mem_id from classmember where class_id= '.$class.')) ORDER BY lastname, firstname ASC') or die(mysqli_error());
while($g_fetch = $g_query->fetch_array()){
echo "";
}
?>

MySQL “NOT IN” query 3 tables

Avoid NOT IN like the plague if

SELECT ID_Courses FROM Evaluation where `NAME`='JOHN' and Year=1

could ever contain NULL. Instead, use NOT EXISTS or Left Joins

use explicit joins, not 1980's style joins using the WHERE clause

To illustrate the misery of NOT IN:

SQL NOT IN () danger

create table mStatus
( id int auto_increment primary key,
status varchar(10) not null
);
insert mStatus (status) values ('single'),('married'),('divorced'),('widow');

create table people
( id int auto_increment primary key,
fullName varchar(100) not null,
status varchar(10) null
);

Chunk1:

truncate table people;
insert people (fullName,`status`) values ('John Henry','single');
select * from mstatus where `status` not in (select status from people);

** 3 rows, as expected **

Chunk2:

truncate table people;
insert people (fullName,`status`) values ('John Henry','single'),('Kim Billings',null);
select * from mstatus where status not in (select status from people);

no rows, huh?

Obviously this is 'incorrect'. It arises from SQL's use of three-valued logic,
driven by the existence of NULL, a non-value indicating missing (or UNKNOWN) information.
With NOT IN, Chunk2 it is translated like this:

status NOT IN ('married', 'divorced', 'widowed', NULL)

This is equivalent to:

NOT(status='single' OR status='married' OR status='widowed' OR status=NULL)

The expression "status=NULL" evaluates to UNKNOWN and, according to the rules of three-valued logic,
NOT UNKNOWN also evaluates to UNKNOWN. As a result, all rows are filtered out and the query returns an empty set.

Possible solutions include:

select s.status
from mstatus s
left join people p
on p.status=s.status
where p.status is null

or use not exists

MySQL NOT IN query taking too long to respond

Inner Join did the trick for me

SELECT DISTINCT c.consumer_id FROM consumer c
INNER JOIN
(SELECT DISTINCT consumer_id as sid from transactions where consumer_id not in (
select consumer_id from transactions
where cycle = '2016-Q-2' AND active_flag = 'Y' AND status != 'Door Locked'
)) as s
ON s.sid = c.consumer_id
WHERE c.active_flag = 'Y' AND ( c.frequency = 'Q' )

not sure if this the correct way but response time is down to ~ 700ms now.

not sure why but the response of queries from all the above answers gave all the consumer ids available in the transaction table.

MySQL not in with COUNT() not working correctly

MySQL supports NOT IN clause, as mentioned in their documentation.

Your query syntax is incorrect. Based on your requirement,

Am i able to Count each Row, that doesnt have the 'yes' in the post_removed column?

Try use this query:

SELECT COUNT(*)
FROM tmp
WHERE post_removed != 'yes'

If there are duplicate values of post_id, then you can apply DISTINCT to get unique values

SELECT COUNT(DISTINCT(post_id))
FROM tmp
WHERE post_removed != 'yes'

Or performing GROUP BY will results the same, even you can get post_removed for each post_id

SELECT post_id, COUNT(*)
FROM tmp
WHERE post_removed != 'yes'
GROUP BY post_id

If the tmp table has many rows, it's better if you put an INDEX on post_removed column.

Mysql select query NOT IN in same table

If the list of products comes from an application, and you know the length of the list as well (in this case 5) you can find the number of non-existent products in the table like this:

SELECT 5 - COUNT(DISTINCT product_id) AS non_existent_products
FROM products
WHERE product_id IN (12, 13, 14, 15, 16)

Output

non_existent_products
2

If you don't know the length of the list, you can figure it out using e.g.

LENGTH('(12, 13, 14, 15, 16)') - LENGTH(REPLACE('(12, 13, 14, 15, 16)', ',', '')) + 1

Demo on dbfiddle

Update

If the other product_id values are coming from another query (e.g. SELECT product_id FROM othertable) you can find the count of products in that result that are not in the products table using a LEFT JOIN of the results from that query with the products table and taking the difference in COUNTs of product_id from the two. Something like this:

SELECT COUNT(DISTINCT q.product_id) - COUNT(DISTINCT p.product_id) AS non_existent_products
FROM (SELECT product_id FROM products2) q
LEFT JOIN products p ON p.product_id = q.product_id

Demo on dbfiddle

MySQL query not displaying all data when using WHERE clause

I believe something like this will do the job for you, as you want to retrieve all the categories for an ambulance id as long as at least one of the categories match.

SELECT DISTINCT 
amb.ID,
amb.CONTACT,
T.CATG
FROM
ambulance as amb
LEFT JOIN multi_category as mct ON
amb.ID = mct.AMBULANCE_ID
LEFT JOIN
(SELECT mct.AMBULANCE_ID,
GROUP_CONCAT(DISTINCT cat.CATEGORY SEPARATOR ', ') AS CATG
FROM multi_category as mct
LEFT JOIN category as cat ON
mct.CATEGORY_ID = cat.ID
GROUP BY mct.AMBULANCE_ID) T
ON T.AMBULANCE_ID = amb.ID
WHERE
mct.CATEGORY_ID = 01
ORDER BY
amb.ID
DESC


Related Topics



Leave a reply



Submit