CODEWITHSUNDEEP
databasesql-server-2008t-sqljoin
MJH
MJH

Reputation: 366

Complex SQL Join

I am fairly new to SQL joins, but I have a tricky issue here. I have tried to resolve this on my own, and searched as well, but unsuccessful.

I have two primary SQL tables

CustProfile 

ClientID || ClientName

CustTransaction 

CorpID || DivID || DeptID

I need to display my output as follows: 

`CorpID` `CorpIDClientName` `DivID` `DivIDName` `DeptID` `DeptIDName`  

CustTransaction.CorpID join on CustProfile.ClientID to get `CorpIDClientName`  
CustTransaction.DivID join on CustProfile.ClientID to get `DivIDName`  
CustTransaction.DeptID join on CustProfile.ClientID to get `DeptIDName`

I hope someone can provide the join query. Thanks in advance

Upvotes: 2

Views: 646

Answers (2)

John Woo
John Woo

Reputation: 263703

try this one:

SELECT  a.CorpID,
        b.ClientName AS CorpIDClientName,
        a.DivID,
        c.ClientName AS DivIDName,
        a.DeptID,
        d.ClientName AS DeptIDName
FROM    CustTransaction a
            INNER JOIN CustProfile b
                on a.CorpID = b.ClientID
            INNER JOIN CustProfile c
                on a.DivID = c.ClientID
            INNER JOIN CustProfile d
                on a.DeptID = d.ClientID

Upvotes: 2

cecilkorik
cecilkorik

Reputation: 1431

Am I understanding correctly? You have Corporations, Divisions, and Departments all stored within the CustProfile table together.

So you are only joining the 2 different tables, but you need to join those 2 tables 3 separate times to get each of the different types of customer (Corp or Div or Dept)

If that's the case, what you need to do is alias the table that you are including multiple times so you can join it as if it were 3 separate tables, one for corps, one for divisions, and one for departments.

I'm not sure if the syntax would be the same in MSSQL, but for most SQL databases your join query would look something like this:

SELECT corps.ClientID CorpID, corps.ClientName CorpIDClientName,
       divs.ClientID DivID, divs.ClientName DivIDName,
       depts.ClientID DeptID, depts.ClientName DeptIDName
FROM CustProfile corps, CustProfile divs, CustProfile depts, CustTransaction t
WHERE t.CorpID = corps.ClientID 
  AND t.DivID = divs.ClientID
  AND t.DeptID = depts.ClientID

That should, I think, more or less do what you want...

Upvotes: 1

Related Questions