CODEWITHSUNDEEP
t-sql
Frank
Frank

Reputation: 2045

SQL query question

I have a table that has about 6 fields. I want to write a SQL statement that will return all records that do not have "England" in the country field, English in the language field & english in the comments field.

What would the sql query be like?

Upvotes: 0

Views: 201

Answers (6)

Sarah Vessels
Sarah Vessels

Reputation: 31630

To ignore case:

SELECT *
FROM SixFieldTable
WHERE LOWER(Country) <> 'england' AND
LOWER(language) <> 'english' AND
LOWER(comments) NOT LIKE '%english%'

Upvotes: 1

MartW
MartW

Reputation: 12538

The above solutions do not appear to account for possible nulls in the columns. The likes of

Where country <> 'England'

will erroneously exclude entries where Country is null, under default SQL Server connection settings.

Instead, you could try using 

IsNull(Country, '') <> 'England'

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146409

Try This

Select * From table
Where Country Not Like '%England%'
 And Language Not Like '%English%'
 And comments Not Like '%English%'

Upvotes: 0

zombat
zombat

Reputation: 94147

Well, your question depends a lot on what DBMS you're using and what your table set up looks like. This would be one way to do it in MySQL or TSQL:

SELECT *
FROM tbl
WHERE country NOT LIKE '%England%' AND language NOT LIKE '%english%'
     AND comments NOT LIKE '%english%';

The way you word your question makes it sound like all these fields could contain a lot of text, in which case the above query would be the way to go. However, more likely than not you'd be looking for exact matches in a real database:

SELECT *
FROM tbl
WHERE country!='England' AND language!='english'
    AND comments NOT LIKE '%english%';

Upvotes: 3

glasnt
glasnt

Reputation: 2973

Are you wanting something like

select * from myTableOfMadness
where country <> 'England'
and language <> 'English'
and comments not like '%english%'

Not sure if you want 'and's or 'or's, or all 'not' comparisons. Your sentence structure is somewhat misleading.

Upvotes: 1

Mike DeFehr
Mike DeFehr

Reputation: 1184

Start with this and modify as necessary:

SELECT *
FROM SixFieldTable
WHERE Country <> 'England'
AND language <> 'english'
AND comments NOT LIKE '%english%'

Hope this helps.

Upvotes: 1

Related Questions