CODEWITHSUNDEEP
sqlsql-server
Simon Foster
Simon Foster

Reputation: 656

Help comparing two lists with sql

Hi my knowledge of SQL is not brilliant so was hoping someone can help with this problem.

I have two lists of data that I have imported into SQL Server. I want to compare the two lists and find out how many are in both etc

Usually I would do a simple join but the only column in common with the two lists is a name so I need to combine it with a like command because in one list there are lots of extra characters stopping a normal join from working.

Can any one help give me the correct SQL syntax to get this to work.

Thanks

Upvotes: 2

Views: 31578

Answers (3)

Larry Lustig
Larry Lustig

Reputation: 50970

Not enough information. You need to tell us:

  1. How many columns you need to join (or approximately join) to compare the lists.

  2. In what way, exactly, the columns need to be manipulated to be considered equivalent.

In general you have two approaches:

  1. Build a comparison with LIKE to compare the columns that are not exactly the same.

  2. Operate on the column(s) with "extra characters" using UPDATE until all the discrepant characters are removed, then apply a regular "=" JOIN.

Upvotes: 0

John K.
John K.

Reputation: 5474

Assuming you have 2 columns in the 2 tables... 

select count(*) 
from tableA a, tableB b 
where a.column1 = b.column1 
and a.column2 = b.column2
You can check for not equal by changing the where clause (like "and a.column1 <> b.column1")

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453298

You can use LIKE in a JOIN. It would be clearer if you provided some example data but it sounds like you want something like this.

SELECT * 
FROM T1 
JOIN T2 ON T1.name LIKE T2.name + '%'

Upvotes: 6

Related Questions