Click here to Skip to main content
14,449,862 members
Rate this:
Please Sign up or sign in to vote.
See more: , +
I have the following table:

id---name-------contry---states
1----usa,ab------usa------ab
2----alb,ac------alb------ac
3----nm----------bun------nm
4----but---------but------nt
5----rsa,ab------rsa------rb
6----Null--------brt------rt

I should take the value from column 'country' and 'states' and check that this values exist in the column 'name'. Show the rows where this values does not exist.(null or only country or only states)
for example: if in the 'country' i have value 'usa', in the column 'name' i already have this 'use' value THEN does not show me this row. Show me the rows where the value 'usa' does not exist. The same with column 'states'

What I have tried:

have no idea what o should try. I try through 'like' or 'declare' but can not find the logic. thank for helping
Posted
Updated 11-Feb-20 4:54am
Rate this:
Please Sign up or sign in to vote.

Solution 2

You might need to add some OR in the query.
Test this for a starter:
SELECT  *
FROM    Table1
WHERE   name NOT LIKE country + '%'
    OR  name NOT LIKE '%' + states
    OR  name IS NULL
   
Comments
I am not a programmer 11-Feb-20 10:19am
   
yeeeeah, this much better, thank you :)
Maciej Los 11-Feb-20 14:28pm
   
5ed!
Rate this:
Please Sign up or sign in to vote.

Solution 1

You are correct that you are looking for the LIKE operator; just remember to use the proper wildcards with it, usage of both is in the referenced documentation below this simple sample
SELECT  *
FROM    [tablename]
WHERE   [name] NOT LIKE 'USA.%'
Reference: LIKE (Transact-SQL) - SQL Server | Microsoft Docs[^]
   
Comments
I am not a programmer 11-Feb-20 9:50am
   
your script is good if you know what exactly data you use. But for now I does not know what data exist in the table. Try to write it as if you do not know what data you need to compare
MadMyche 11-Feb-20 12:21pm
   
If you don't know what value(s) you are looking for.... then you don't you the WHERE clause at all.
Maciej Los 11-Feb-20 14:29pm
   
Good one!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100