NOT query syntax.

[Archanak]

Hi,

I have two words regulate and control.

I want to retrieve data from table only for regulate i.e it should not get the rows that has word control.

I tried using LIKE and like but it is giving syntax error. I am using 3.23 version.

query is like this:

select * from table1 where col_name like '%regulate%'  NOT col_name like '%control%';

How to get only records related to regulate but not control?

with regards
Archana

[write2ashokkumar]

Hi,

I have two words regulate and control.

I want to retrieve data from table only for regulate i.e it should not get the rows that has word control.

I tried using LIKE and like but it is giving syntax error. I am using 3.23 version.

query is like this:

[CODE=mysql]select * from table1 where col_name like '%regulate%' NOT col_name like '%control%';[/CODE]

How to get only records related to regulate but not control?

with regards
Archana

Hi,
Your query syntax is wrong. So try with the following one.
Query:
[code=mysql]
select tbl1.*
from table1 tbl1
inner join tlb2 on tb1.primary_id_ col = tbl2.primary_id _col /* Table Self Join Condition */
where tbl1.col_name like '%regulate%' and
tbl2.col_name not like '%control%';[/code]

Regards,
S.Ashokkumar

Please enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

MODERATOR

[write2ashokkumar]

Hi,

I have two words regulate and control.

I want to retrieve data from table only for regulate i.e it should not get the rows that has word control.

I tried using LIKE and like but it is giving syntax error. I am using 3.23 version.

query is like this:

[CODE=mysql]select * from table1 where col_name like '%regulate%' NOT col_name like '%control%'[/CODE]

How to get only records related to regulate but not control?

with regards
Archana

Hi,
One more way is given below,[code=mysql]
select * from table1 where col_name like '%regulate%' AND col_name NOT like '%control%';[/code]

Regards,
S.Ashokkumar

Please enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

MODERATOR

[Archanak]

Hi,

Your query syntax is wrong. So try with the following one.

Query:

select tbl1.*
from table1 tbl1
inner join tlb2 on tb1.primary_id_ col = tbl2.primary_id _col /* Table Self Join Condition */
where tbl1.col_name like '%regulate%' and
tbl2.col_name not like '%control%';

Regards,
S.Ashokkumar

Hi,

I don't have any primary key its just one table with data.

In that case how to give?

with regards
Archana

[write2ashokkumar]

Hi,

Try with the following query,
[code=mysql]select * from table1 where col_name like '%regulate%' AND col_name NOT like '%control%';[/code]
Regards,
S.Ashokkumar

Please enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

MODERATOR

[amitpatel66]

Hi,

I have two words regulate and control.

I want to retrieve data from table only for regulate i.e it should not get the rows that has word control.

I tried using LIKE and like but it is giving syntax error. I am using 3.23 version.

query is like this:

select * from table1 where col_name like '%regulate%'  NOT col_name like '%control%';

How to get only records related to regulate but not control?

with regards
Archana

If you are looking at data related to regulate and not any other condition, then this simple query would do:

[code=mysql]

SELECT * FROM table_name WHERE UPPER(col_name) LIKE '%REGULATE%'

[/code]

Once you check for REGULATE in your WHERE Condition, you need not worry about checking for NOT IN CONTROL beucase the query will give you only those records that matches with REGULATE

[amitpatel66]

If you are looking at data related to regulate and not any other condition, then this simple query would do:

[code=mysql]

SELECT * FROM table_name WHERE UPPER(col_name) LIKE '%REGULATE%'

[/code]

Once you check for REGULATE in your WHERE Condition, you need not worry about checking for NOT IN CONTROL beucase the query will give you only those records that matches with REGULATE

PS: But yes, if a column contains any value like 'REGULATE CONTROL', then it will get fetched by the above query

[write2ashokkumar]

PS: But yes, if a column contains any value like 'REGULATE CONTROL', then it will get fetched by the above query

Hi,

yes, its correct. so that only we can try with the following query,
[code=mysql]select * from table1 where col_name like '%regulate%' AND col_name NOT like '%control%';[/code]
Regards,
S.Ashokkumar

Please enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

MODERATOR