MYSQL selecting DISTINCT value WHERE
Hi I have this problem I can’t work out.
I’m using an autocomplete search to try to get a unique value out of a database table and I’m using the following statement.
SELECT DISTINCT trackingChipNumber FROM `main` WHERE `trackingChipNumber` LIKE ? ---------- trackingChipNumber|Fate ---------------------------- DESTR2339442 |Relocated FERW23445212 |Relocated DESTR2339442 |Died ---------------------------- As you type it narrows the search of the records down. Currently it returns DESTR2339442 and FERW23445212
What I need to do is remove any values where a Fate of Died has occurred.
The search should only return FERW23445212 as DESTR2339442 has since passed away.
I just can’t work out how to do that.
TIA
try this
SELECT DISTINCT t1.trackingChipNumber FROM `main` t1 left join `main` t2 on t2.trackingChipNumber = t1.trackingChipNumber and t2.fate = 'Died' WHERE t2.fate is null and t1.trackingChipNumber LIKE ? 
My answer is the same as @Ronny’s answer. I also add some performance turning suggestion for your SQL. All my sql is verified on Mysql Community Server 5.6.48.
Please try the following SQL.
select distinct t1.trackingChipNumber from main as t1 left join main as t2 on t2.trackingChipNumber = t1.trackingChipNumber and t2.fate in ('Died') where t1.trackingChipNumber LIKE "%" and t2.fate is null I’m not sure how you add index for this table and what the defination of this table too. The following is the sql what I used to create this table:
create table `main` ( `id` int(11) not null auto_increment primary key, `trackingChipNumber` varchar(20) not null, `fate` varchar(10) not null, KEY `index_tcnum_fate` (`trackingChipNumber`, `fate`)) engine = InnoDB; I’m not sure if this is suitable for your case, however, after adding such index, the query in my sandbox could be more fast. The coresponding execute plans are as following.
Before adding index, all data will be scan twice in main table.
mysql> explain select -> distinct t1.trackingChipNumber -> from main as t1 left join -> main as t2 -> on t2.trackingChipNumber = t1.trackingChipNumber and t2.fate in ('Died') -> where t1.trackingChipNumber LIKE "%" and t2.fate is null; +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using temporary | | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Not exists; Distinct; Using join buffer (Block Nested Loop) | +----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------------------------------------------------+ 2 rows in set (0.00 sec) After adding index, index will be used.
mysql> explain select -> distinct t1.trackingChipNumber -> from main as t1 left join -> main as t2 -> on t2.trackingChipNumber = t1.trackingChipNumber and t2.fate in ('Died') -> where t1.trackingChipNumber LIKE "%" and t2.fate is null; +----+-------------+-------+-------+------------------+------------------+---------+-------------------------------+------+------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+------------------+------------------+---------+-------------------------------+------+------------------------------------------------+ | 1 | SIMPLE | t1 | index | index_tcnum_fate | index_tcnum_fate | 34 | NULL | 3 | Using where; Using index; Using temporary | | 1 | SIMPLE | t2 | ref | index_tcnum_fate | index_tcnum_fate | 22 | lzytest.t1.trackingChipNumber | 1 | Using where; Not exists; Using index; Distinct | +----+-------------+-------+-------+------------------+------------------+---------+-------------------------------+------+------------------------------------------------+ 2 rows in set (0.00 sec) 
