Fin correct SQL Syntax

I have two tables A and B with column fields "Phone Number". BTW, the link of the two tables is the field UNITID
What Id like to prove are:

1) Find IN Table A, check all Phone numbers that match with Table B

2} Find all Phone numbers in Table A that do not match in Table B or present in Table A and not in Table B

3) Also find all Phone Numbers in table B that do not match in Table A
zachvaldezAsked:
Who is Participating?
 
Nitin SontakkeDeveloperCommented:
May be you should have posted the table structure and some sample data and the expected output. That way it is lot easier / quicker for us to just put the query together and determine if expectations are met or not.

In the absence of such a data, here is a real crud query which you wish to / have to modify.

select 
   coalesce(a.[UnitId], b.[UnitId]) [UnitId]
  ,coalesce(a.[PhoneNumber], b.[PhoneNumber]) [PhoneNumber]
  ,case when a.[UnitId] is not null and b.[UnitId] is not null and a.[PhoneNumber] is not null and b.[PhoneNumber] is not null then 'Yes' else 'No' end [Matched In Both]
  ,case when a.[UnitId] is not null and b.[UnitId] is null and a.[PhoneNumber] is not null and b.[PhoneNumber] is null then 'Yes' else 'No' end [Missing in B]
  ,case when a.[UnitId] is null and b.[UnitId] is not null and a.[PhoneNumber] is null and b.[PhoneNumber] is not null then 'Yes' else 'No' end [Missing in A]
from tableA a
full outer join tableB b on a.[UnitId] = b.[UnitId] and a.[PhoneNumber] = b.[PhoneNumber]

Open in new window


Hope it helps!
0
 
Bill PrewCommented:
Are you looking for 1 query, or three query's?


»bp
0
 
awking00Commented:
1) Find IN Table A, check all Phone numbers that match with Table B
select phone_number from A intersect select phone_number from B

2} Find all Phone numbers in Table A that do not match in Table B or present in Table A and not in Table B
select phone_number from A except select phone_number from B

3) Also find all Phone Numbers in table B that do not match in Table A
select phone_number from B except select phone_number from A
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
awking00Commented:
The previous was for SQL Server. Depending on your DBMS (e.g. Oracle), you might have to substitute minus instead of except
0
 
zachvaldezAuthor Commented:
DBMS is SQL Server
0
 
zachvaldezAuthor Commented:
I'm looking for 3 queries.
0
 
zachvaldezAuthor Commented:
I did not see an inner join by UNITID between table A and B added in the select statement.
0
 
zachvaldezAuthor Commented:
here's example of the table structure

Table A                                       Table B
UnitID                                           UnitID
UnitType                                     Address
PrinName                                    City
Phone                                              Phone
WEbAddress
0
 
zachvaldezAuthor Commented:
Is it possible to just create a view and query against it?
0
 
awking00Commented:
Are there any UnitID values that exist in one table and not the other? Would still like to see some sample data and your expected output.
0
 
zachvaldezAuthor Commented:
there is a need to join 2 tables
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.