SQL query with subquery

Question

My data is like this:

data1_qqq_no_abc_ccc
data1_qqq_abc_ccc
data2_qqq_no_abc_ccc
data2_qqq_abc_ccc
data3_qqq_no_abc_ccc
data4_qqq_no_abc_ccc
data4_qqq_abc_ccc

...

Now I want to get the fields where data has substring _no_abc_ccc, but doesn't have _abc_ccc. In the above example, its data3

I am trying to create a query for it. rough one is

select SUBSTRING_INDEX(name, 'abc', 1)  
from table1 
where SUBSTRING_INDEX(name, 'abc', 1) not LIKE "%no" 
  and NOT IN (select SUBSTRING_INDEX(name, '_no_abc', 1) 
              from table 
              where name LIKE "%no_abc");

@MadhurBhaiya I only need rows which has no-abc but doesn't have "abc". For eg, data3 only in the above example. where name like '%no-abc%' will give me all the rows like data1, data2, data3, data4 — Harsh Sharma, Sep 25 '18 at 18:35
@jarlh Yeah. Basically I need fields which has _no-abc, but doesn't have _abc. For eg data3 in the above example. — Harsh Sharma, Sep 25 '18 at 18:40
select name from table1 where name like '%no-abc%' and in ( select name from table1 where not like '%_abc%') — user4321, Sep 25 '18 at 18:42
@jarlh I changed the question a little bit. Earlier I have not put the exact format. Sorry for the confusion. — Harsh Sharma, Sep 25 '18 at 19:07

score 5 · Accepted Answer · edited Sep 25 '18 at 19:14

5

Something like this (?)

create table t (
    col text
);

insert into t
values
('data1_qqq_no_abc_ccc'),
('data1_qqq_abc_ccc'),
('data2_qqq_no_abc_ccc'),
('data2_qqq_abc_ccc'),
('data3_qqq_no_abc_ccc'),
('data4_qqq_no_abc_ccc'),
('data4_qqq_abc_ccc');

select f from (
    select SUBSTRING_INDEX(col, '_', 1) as f, SUBSTRING_INDEX(col, '_', -3) as s from t
) tt
group by f
having 
count(case when s = 'no_abc_ccc' then 1 end) > 0
and
count(case when s like '%qqq_abc%' then 1 end)  = 0

demo

edited Sep 25 '18 at 19:14

Harsh Sharma

10,942
2
18
29

answered Sep 25 '18 at 18:41

Oto Shavadze

40,603
55
152
236

@jarlh - I know this feeling )) – Oto Shavadze Sep 25 '18 at 18:46
@OtoShavadze thanks alot for the answer. But just realized the data format is more weird than I wrote and the above answer is not working for the exact data format. I reedited the question. So sorry for the trouble. – Harsh Sharma Sep 25 '18 at 18:56

SQL query with subquery

1 Answers1