CODEWITHSUNDEEP
sqlsql-serverdatabase
tim
tim

Reputation: 901

SQL Server : many-to-Many Join into Single Row

I'm relatively new to SQL Server, so bear with me if I'm missing something obvious.

I have a database with tables Retailers, Wholesalers, and RetailerWholesaler.

Currently each retailer can have zero, one, or two wholesalers (but of course that could change with little notice!). I'm having trouble creating a view that has columns for RetailerID, PrimaryWholesalerID, and SecondaryWholesalerID. No matter what I've tried, I keep ending up with Primary/Secondary on separate rows:

RetailerID | PrimaryWholesalerID | SecondaryWholesalerID
-----------+---------------------+----------------------
       100 | 8888                | NULL
       100 | NULL                | 9999

What I'm trying to get to is:

RetailerID | PrimaryWholesalerID | SecondaryWholesalerID
-----------+---------------------+----------------------
       100 | 8888                | 9999

And this is the script I currently have:

SELECT 
    r.RetailerID,
    CASE WHEN rw.RetailerWholesalerType = 'Primary'
            THEN w.WholesalerID END PrimaryWholesalerID
    CASE WHEN rw.RetailerWholesalerType = 'Secondary'
            THEN w.WholesalerID END SecondaryWholesalerID
FROM
    Retailers r
LEFT OUTER JOIN 
    RetailerWholesaler rw ON r.RetailerID = rw.RetailerID
LEFT OUTER JOIN 
    Wholesalers w ON rw.WholesalerID = w.WholesalerID

I still want to show the RetailerID even if there's no primary or secondary wholesaler.

I'm using SQL Server 2017. Thanks in advance for any help or insight.

Upvotes: 1

Views: 65

Answers (2)

John Woo
John Woo

Reputation: 263723

You can conditionally aggregate the result by using MAX()

SELECT r.RetailerID,
       MAX(CASE WHEN rw.RetailerWholesalerType = 'Primary'
                THEN w.WholesalerID END) PrimaryWholesalerID
       MAX(CASE WHEN rw.RetailerWholesalerType = 'Secondary'
                THEN w.WholesalerID END) SecondaryWholesalerID
FROM Retailers r
     LEFT OUTER JOIN RetailerWholesaler rw
         ON r.RetailerID = rw.RetailerID
     LEFT OUTER JOIN Wholesalers w
         ON rw.WholesalerID = w.WholesalerID
GROUP BY r.RetailerID

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would suggest writing this using aggregation:

SELECT r.RetailerID,
       MAX(CASE WHEN rw.RetailerWholesalerType = 'Primary'
                THEN w.WholesalerID
           END) as PrimaryWholesalerID
       MAX(CASE WHEN rw.RetailerWholesalerType = 'Secondary'
                THEN w.WholesalerID
           END) as SecondaryWholesalerID
FROM Retailers r LEFT OUTER JOIN
     RetailerWholesaler rw
     ON r.RetailerID = rw.RetailerID LEFT OUTER JOIN
     Wholesalers w
     ON rw.WholesalerID = w.WholesalerID
GROUP BY r.RetailerID;

Upvotes: 2

Related Questions