CODEWITHSUNDEEP
sqlsql-servert-sql
Tim
Tim

Reputation: 1249

Distinct SQL that will return true if in a table

I'm drawing a blank on the best way write this sql, For simplicity sake I'm Only interested in one column. Table A Column = [Supplier]. Table B Column = [AuthorizedSuppliers]

Table A contains many rows with the same value in the [Supplier]. 

Table A
------------------------------
ID |Supplier      | Company  |
---|--------------|----------|
1  | Warehouse 1  | Company 1|
2  | Warehouse 1  | Company 2|
3  | Warehouse 1  | Company 3|
4  | Warehouse 2  | Company 4|
5  | Warehouse 2  | Company 5|
6  | Warehouse 3  | Company 6|
7  | Warehouse 3  | Company 7|
8  | Warehouse 3  | Company 8|

Table B contains a single Supplier from Table A [Supplier] row.

Table B
------------------------------
ID |AuthorizedSupplier       |
---|-------------------------|
1  | Warehouse 1             | 
2  | Warehouse 2             |

I need to return the DISTINCT values of [Supplier] and [AuthorizedSupplier] with a additional column let's call it [Authorized] that is marked as true for those values that were in Table B

So the result set of the above sample should look like this

Result Set
------------------------------
Supplier    |Authorized      |
------------|----------------|
Warehouse 1 | True           |
Warehouse 2 | True           |
Warehouse 3 | False          |

Upvotes: 0

Views: 714

Answers (3)

user3889790
user3889790

Reputation: 11

SELECT A.Supplier
     , CASE WHEN B.AuthorizedSupplier IS NULL
          THEN 'True' ELSE 'False' END  AS Authorized
 FROM (Select Distinct Supplier
         FROM TableA)          A
        LEFT OUTER JOIN  TableB

Upvotes: 1

huMpty duMpty
huMpty duMpty

Reputation: 14460

Select distinct A.Supplier, (Case When B.Id IS NULL Then 0 else 1 end) Authorized
from TableA A
    Left Outer Join TableB  B ON A.Supplier = B.AuthorizedSupplier

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460158

Use Distinct and Left Outer Join:

SELECT DISTINCT Supplier, Authorized = CASE WHEN B.AuthorizedSupplier IS NULL
                                       THEN 'True' ELSE 'False' END
FROM TableA A
LEFT OUTER JOIN TableB B
  ON A.Supplier = B.AuthorizedSupplier

SQL-Fiddle Demo

Upvotes: 2

Related Questions