CODEWITHSUNDEEP
sqlsql-server
dreadcrumb
dreadcrumb

Reputation: 191

How can I show one element from one of two tables in a column

Table A has the id for a Company of which the name can be either in table B or table C. What I'm trying to achieve is to show the fetched name in a new column, something like:

SELECT Bank.Name OR Company.Name AS 'CompanyName'
FROM Account 
INNER JOIN Company
ON Account.CompanyID = Company.CompanyID OR
Account.CounterpartyID = Company.CompanyID
INNER JOIN Bank
ON Account.CompanyID = Bank.BankID OR
Account.CounterpartyID = Bank.BankID 
WHERE ...

Can this work like this or do I need to use multiple SELECTs?

Thanks in advance!

Upvotes: 2

Views: 93

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I would recommend LEFT JOIN. You seem to want names for both the company and the counterparty. That would be two columns and additional joins:

SELECT COALESCE(c.Name, b.Name) AS CompanyName,
       COALESCE(cc.Name, bc.Name) AS CounterpartyName,
FROM Account a LEFT JOIN
     Company c
     ON a.CompanyID = c.CompanyID LEFT JOIN
     Bank b
     ON a.CompanyID = b.BankID AND
        c.CompanyID IS NULL LEFT JOIN
     Company cc
     ON a.CounterpartyID = cc.CompanyID LEFT JOIN
     Bank bc
     ON a.CounterpartyID = bc.BankID AND
        cc.CompanyID IS NULL
WHERE ...

Upvotes: 1

Ilyes
Ilyes

Reputation: 14928

Yes, it can be done with COALESCE() function if you have NULLs, or even IIF() function or CASE expression

Instead of SELECT Bank.Name OR Company.Name AS 'CompanyName' you can do

SELECT COALESCE(Bank.Name, Company.Name) AS CompanyName

OR

SELECT IIF(Bank.Name IS NULL, Company.Name, Bank.Name) AS CompanyName

OR

SELECT CASE WHEN Bank.Name IS NULL
            THEN Company.Name
            ELSE Bank.Name
       END  AS CompanyName

If you mean '' then

SELECT CASE WHEN Bank.Name = ''
            THEN Company.Name
            ELSE Bank.Name
       END  AS CompanyName

Upvotes: 1

Rob
Rob

Reputation: 45780

Assumptions:

  • There will be ONE, or NO rows in TableA or TableB for the company
  • If there is a row in both TableA and TableB then taking the company name from TableA is acceptable

With those assumptions this code should do the trick:

SELECT  A.CompanyId,
        COALESCE(B.CompanyName, C.CompanyName)
FROM    TableA A
LEFT
JOIN    TableB B
        ON A.CompanyId = B.CompanyId
LEFT
JOIN    TableC C
        ON A.CompanyId = C.CompanyId
WHERE   TableA.CompanyId = 1234

Essentially what this code is doing is joining the tables together, using a LEFT JOIN so that rows from TableA don't get excluded by the absence of a row in either of TableB or TableC, then using COALESCE to get the first non-NULL value from either of the two tables for the CompanyName column.

The result of the query (if you were using SELECT *) where TableA doesn't contain a row would look a little like this:

A visual representation of the result set the query would operate against

Just to give an example of the data the query would be working against.

Upvotes: 2

Konstantin Schütte
Konstantin Schütte

Reputation: 1009

SELECT
CASE
    WHEN EXISTS(
        SELECT b.name FROM tableB b WHERE b.name IS NOT NULL
    ) THEN b.name AS 'CompanyName'

    WHEN EXISTS(
        SELECT c.name FROM tableC c WHERE c.name IS NOT NULL
    ) THEN c.name AS 'CompanyName'
END
FROM tableA a
...

Hope this will work for you!

Upvotes: 1

Related Questions