How to compare value from the first column with value from second one

Question

1.00/5 (1 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 11-Feb-20 3:30am

I am not a programmer

Updated 11-Feb-20 3:54am

Add a Solution

2 solutions

Add a Solution

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

Jörgen Andersson · Answer 1 · 2020-02-11T03:55:00

Solution 2

You might need to add some OR in the query.
Test this for a starter:

SQL

SELECT  *
FROM    Table1
WHERE   name NOT LIKE country + '%'
    OR  name NOT LIKE '%' + states
    OR  name IS NULL

Posted 11-Feb-20 3:55am

Jörgen Andersson

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!

MadMyche · Answer 2 · 2020-02-11T03:41:00

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

SQL

SELECT  *
FROM    [tablename]
WHERE   [name] NOT LIKE 'USA.%'

Reference: LIKE (Transact-SQL) - SQL Server | Microsoft Docs[^]

Posted 11-Feb-20 3:41am

MadMyche

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!

How to compare value from the first column with value from second one

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

How to compare value from the first column with value from second one

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us