CODEWITHSUNDEEP
sqlsql-serverjoinmany-to-many
BastianBuhrkall
BastianBuhrkall

Reputation: 348

SQL many-to-many JOIN

i am having difficulties joining two tables.

I have the tables

Customer_table
 ---------------------------------------
| CustomerId(PK) | Firstname | Lastname |
 ---------------------------------------

CustomerInterest_table
 ----------------------------------------
| CustomerId(PK,FK) | InterestId(PK,FK)  |
 ----------------------------------------

Interest_table
 -------------------------------
| InterestId(PK) | InterestInfo |
 -------------------------------

What i want to do is to select every customer, and join the interests with the FK reference on the table.

Ultimately i want to fetch a result containing the customers fetched from the customer table, and the customer interests fetched from the CustomerInterest_table.

Id like to build objects like this 

{
customerId : 'Id12345,
firstname : 'John', 
lastname : 'Doe', 
interests : [{interestId : 1, interestInfo : 'Apples'}]
}

How would i go about fetching and joining the tables ? Any help greatly appreciated.

Upvotes: 1

Views: 104

Answers (1)

Dewfy
Dewfy

Reputation: 23614

Database design (First Normal Form) suppose that column should be simple type, in you case it mean no-array. Instead you can fetch desired from multiple selected rows:

SELECT customerId, firstname, lastname, interestId, InterestInfo 
    FROM Customer_table c
    INNER JOIN CustomerInterest_table tc
        ON c.customerId = tc.customerId
    INNER JOIN Interest_table i 
        ON tc.InterestId = i.InterestId
ORDER BY customerId

Last ORDER BY allows you force order of rows so interests of the same customer will follow one by one.

Alternatively if customer MAY not have interests you can leverage LEFT JOIN (then two columns interestId, InterestInfo will be NULL)

SELECT customerId, firstname, lastname, interestId, InterestInfo 
    FROM Customer_table c
    LEFT OUTER JOIN CustomerInterest_table tc
        ON c.customerId = tc.customerId
    INNER JOIN Interest_table i 
        ON tc.InterestId = i.InterestId
ORDER BY customerId

UPDATE

Alternatively (if you really want everything in single column for any cost) you can cast result to XML datatype, Then Last column will compose complex XML:

SELECT customerId, firstname, lastname
 , [Name]
 , (STUFF((SELECT CAST(', ' + interestId AS VARCHAR(MAX) + ':' + InterestInfo) 
     FROM Interest_table i
     WHERE tc.InterestId = i.InterestId
     FOR XML PATH ('')), 1, 2, '')) AS interests 
FROM Customer_table c
    INNER JOIN CustomerInterest_table tc
        ON c.customerId = tc.customerId

(p.s. sorry syntax is not checked for correctness)

Upvotes: 3

Related Questions