CODEWITHSUNDEEP
sqlsql-serverjoindistinctdbeaver
JackSparrow123
JackSparrow123

Reputation: 1380

Finding Distinct Values across 2 tables is not working

I have 2 tables (I've only shown select fields):

Table A:

    <LOCATION CODE>
    ACT   
    NSWNS    
    OSUSA
    Parra
    Parra
    Parra
    ACT
    ACT

Table B (A lookup table):

<CODE>    <DESCRIPTION>
ACT        Australian Capital Territory
NSWNS      New South Whales - North South
OSUSA      Overseas - USA
Parra      Parramatta

"Description" is of type 'text'. "Code" is of type 'varchar'. "location" is a type of t_code_key.

I'm trying to perform join on both of these table so that I receive a unique list of 'Code' items from Table A long with their full description name from Table B.

I want to get something like this:

<CODE>   <DESCRIPTION>
ACT      Australian Capital Territory
Parra    Parramatta
NSW      New South Whales

I've tried the following two queries I wrote but I'm not getting any luck just errors:

select mp.location, ct.description
from 
    (select DISTINCT CAST(description AS VARCHAR(MAX)) AS ct FROM code_table)
    full outer JOIN
    (select DISTINCT location from mailout_profile) as mp 
        on ct.code = mp.location;

select mp.location, ct.description
from 
    (select DISTINCT convert(VARCHAR(MAX), description) AS ct FROM code_table)
    full outer JOIN
    (select DISTINCT location from mailout_profile) as mp 
        on ct.code = mp.location;

The errors I'm getting are:

SQL Error [156] [S0001]: Incorrect syntax near the keyword 'full'.
  Incorrect syntax near the keyword 'full'.
  Incorrect syntax near the keyword 'full'.

Would appreciate help thanks!! :)

Upvotes: 0

Views: 509

Answers (2)

Mukesh Arora
Mukesh Arora

Reputation: 1813

You can use the below query to get the desired result -

Sample Data

Declare @TableA table
(LocationCode varchar(20))

Insert into @TableA
values

    ('ACT'   ),
    ('NSWNS' ),   
    ('OSUSA'    ),
    ('Parra'    ),
    ('Parra'    ),
    ('Parra'    ),
    ('ACT'  ),
    ('ACT'  )


Declare @TableB table
(Code varchar(20), Description varchar(100))

Insert into @TableB
values
('ACT'    ,  'Australian Capital Territory'   ),
('NSWNS'  ,  'New South Whales - North South' ),
('OSUSA'  ,  'Overseas - USA'                 ),
('Parra'  ,  'Parramatta'                     )

Query

select DISTINCT LocationCode, [Description] 
From @TableA a inner join @TableB b on a.LocationCode = b.Code

Upvotes: 1

Saeid Amini
Saeid Amini

Reputation: 1307

Please try this:

WITH r1 (loc) AS
    (SELECT DISTINCT LOCATION FROM [mailout_profile])
SELECT Code, Description FROM r1
FULL OUTER JOIN [code_table] ct ON r1.loc =  ct.Code

enter image description here

Upvotes: 1

Related Questions