'SQL Query Adding Another Table w/One To Many Relationship [duplicate]

We have this query that works well within written in our Coldfusion application. We have a Businesses, BuisnessesType, and new BusinessesToBusinessesType table. The latter because the client wants us to make a business to be able to have multiple business types associated with it (plus the User table). The BusinessesToBusinessesType table can have multiple records with same BusinessID and different BusinessesTypeID's from the BusineessType table. The goal is to get a list of those BusinessesTypeID's that are outputted from the query for each record. Currently, it's just got one BusinessesType for each record. The change will be to allow businesses to have have multiple types associated with it.

Tables & Fields:

Businesses (BusinessID, Business, Latitude, Longitiude, ApprovedBy, ApprovedDate, CreatedBy, UpdatedBy, isActive)

BusinessesType (BusinessesTypeID, BusinessesTypeName, CreatedBy, UpdatedBy, IsActive) 

BusinessesToBusinessesType (BusinessesToBusinessesTypeID, BusinessID, BusinessesTypeID, CreatedBy, UpdatedBy, isActive) 



Sample data:

Businesses: BusinessA, BusinessB, BusinessC, BusinessD, BusinessE

Business Types: restaurant, bar, race track, theater, concert venue, retail, commercial, mixed use, apartment.

Here is the query in it's current working state. Minus all the search criteria after the Where statement:

    SELECT 
    Businesses.BusinessID, 
    Businesses.Business, 
    BusinessesType.BusinessesTypeID,
    BusinessesType.BusinessesTypeName, 
    CASE WHEN Businesses.Approved = 1 THEN 'Yes ' ELSE 'No ' END AS Approved, 
    Businesses.ApprovedBy, 
    Businesses.latitude, 
    Businesses.longitude, 
    CASE WHEN Businesses.isActive = 1 THEN 'Enabled' ELSE 'Disabled' END AS isActive, 
    Created.FirstName AS Created_FirstName, 
    Created.LastName AS Created_LastName, 
    Updated.FirstName AS Updated_FirstName, 
    Updated.LastName AS Updated_LastName, 
    Approver.FirstName AS Approver_FirstName, 
    Approver.LastName AS Approver_LastName

    FROM Businesses 

    LEFT OUTER JOIN BusinessesToBusinessesType ON Businesses.BusinessID = BusinessesToBusinessesType.BusinessID 
    LEFT OUTER JOIN BusinessesType ON BusinessesToBusinessesType.BusinessesTypeID = BusinessesType.BusinessesTypeID 
    LEFT OUTER JOIN [User] AS Created ON Businesses.CreatedBy = Created.UserNUM 
    LEFT OUTER JOIN [User] AS Updated ON Businesses.UpdatedBy = Updated.UserNUM 
    LEFT OUTER JOIN [User] AS Approver ON Businesses.ApprovedBy = Approver.UserNUM

    WHERE        (1 = 1)

    ORDER BY Businesses.BusinessID ASC
sqlsql-serverstring-concatenation


Solution 1:[1]

Easy way to do this with current versions is to use STRING_AGG (old trick was to use XML functions and STUFF)

Microsoft Docs here

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Hogan

Related Questions