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
- 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.
- you have not properly ended brackets.
Close brackets properly.
As per comment, You have also one more issue.
- 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 COUNT
s 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
How to Create Multiple One to One's
How to Force Postgres to Use a Particular Index
Table-Less Union Query in Ms Access (Jet/Ace)
How to Restore a Dump File from MySQLdump
Normalize Array Subscripts So They Start With 1
Safely Rename Tables Using Serial Primary Key Columns
Decision Between Storing Lookup Table Id'S or Pure Data
How to Fetch the Row Count for All Tables in a SQL Server Database
SQL Server:Dynamic Pivot Over 5 Columns
MySQL Select Dynamic Row Values as Column Names, Another Column as Value
How to Create a Real One-To-One Relationship in SQL Server
How to Do a Case Sensitive Search in Where Clause (I'M Using SQL Server)
Microsoft Jet Wildcards: Asterisk or Percentage Sign
Real Life Example, When to Use Outer/Cross Apply in Sql
SQL - Subtracting a Depleting Value from Rows
Constraint Defined Deferrable Initially Immediate Is Still Deferred