473,320 Members | 1,977 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

NOT query syntax.

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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. select * from table1 where col_name like '%regulate%'  NOT col_name like '%control%';
  3.  
  4.  
How to get only records related to regulate but not control?

with regards
Archana
Mar 28 '08 #1
7 1360
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:

Expand|Select|Wrap|Line Numbers
  1. 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
Hi,
Your query syntax is wrong. So try with the following one.
Query:
Expand|Select|Wrap|Line Numbers
  1. select tbl1.* 
  2. from     table1 tbl1 
  3.     inner join tlb2 on tb1.primary_id_col = tbl2.primary_id_col  /* Table Self Join Condition */
  4. where     tbl1.col_name like '%regulate%'  and
  5.        tbl2.col_name not like '%control%';
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
Mar 28 '08 #2
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:

Expand|Select|Wrap|Line Numbers
  1. 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
Hi,
One more way is given below,
Expand|Select|Wrap|Line Numbers
  1. select * from table1 where col_name like '%regulate%'  AND col_name NOT like '%control%';
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
Mar 28 '08 #3
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
Mar 28 '08 #4
Hi,

Try with the following query,
Expand|Select|Wrap|Line Numbers
  1. select * from table1 where col_name like '%regulate%' AND col_name NOT like '%control%';
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
Mar 28 '08 #5
amitpatel66
2,367 Expert 2GB
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. select * from table1 where col_name like '%regulate%'  NOT col_name like '%control%';
  3.  
  4.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT * FROM table_name WHERE UPPER(col_name) LIKE '%REGULATE%'
  3.  
  4.  
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
Mar 28 '08 #6
amitpatel66
2,367 Expert 2GB
If you are looking at data related to regulate and not any other condition, then this simple query would do:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT * FROM table_name WHERE UPPER(col_name) LIKE '%REGULATE%'
  3.  
  4.  
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
Mar 28 '08 #7
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,
Expand|Select|Wrap|Line Numbers
  1. select * from table1 where col_name like '%regulate%' AND col_name NOT like '%control%';
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
Mar 28 '08 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: joemyre | last post by:
Hi everyone, What I'm trying to do is take php variables i got from user input, and pass them as the MySQL query terms. $query = "select * from ident where ".$searchtype1."=".$searchterm1."";...
29
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules"...
3
by: Paradigm | last post by:
I want to create a pass through query in Access to get data from a MYSQL table. I need to do this using code so that sertain aspects of the query can be changed. When I look at the SQL version of...
4
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET...
3
by: KevLow | last post by:
Hi, Hope some kind soul can help me out here.. I'm trying to programmatically modify the column headings of a crosstab query such that it can be dynamic based on user specified period (Month...
6
by: Patrick A | last post by:
All - I have the following in the Field row of a column in a query that runs fine against a local table: MSA: InStr(!!,!) I'm now trying to run the query against a linked DB2 table. I get...
1
by: Crash | last post by:
Hi, ..NET v1.x SP1 VS 2003 SQL Server 2000 SP3 Server 2000, XP, Server 2003 I would like to programmatically execute {possibly many} SQL Server batch scripts. Aka I have many scripts that...
6
by: windandwaves | last post by:
Hi Folk I have a query: SELECT COUNT( `SIS`.`ID` ) c, D FROM `SIS` , `SID` WHERE `SID_ID` = `SID`.`ID` AND `BRO` <> "bot" GROUP BY SID.ID
10
by: Daniel | last post by:
In Microsoft Access I can write a query that includes the criteria: Between Date()-7 And Date() to retrieve the records from a table that fall within the last week. I'm trying to write a...
7
by: bryant | last post by:
Hi all. I am new to ASP and working in Expression Web. The following query displays the information I need in the gridview for a single record. SELECT "OE_HDR"."ORD_NO", "OE_HDR"."CUST_NAM",...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.