SQL Query Adding Another Table w/One To Many Relationship

Question

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

Try [STRING_AGG()](https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15). If you need a more specific answer, put together a [fiddle](http://sqlfiddle.com/) with some sample data. — SOS, Apr 13 '22 at 01:18
Business types example: restaurant, bar, race track, theater, concert venue, retail, commercial, mixed use, apartment, condo, single family, duplex, townhouse. Therefore, some businesses may have have multiple types associated with it. Like one could be a bar and concert venue. — FSUKXAZ, Apr 15 '22 at 14:24
The way the query works right now is it lists one business type for each business. I want it to be able to list all the business types associated with it (which should be a max of like 3 or 4). — FSUKXAZ, Apr 15 '22 at 14:26
Use STUFF https://stackoverflow.com/questions/21760969/multiple-rows-to-one-comma-separated-value-in-sql-server — Vikrant Shitole, Apr 15 '22 at 23:16
I see how that works with one record, but I have almost five hundred records. How do I use that with my query above? — FSUKXAZ, Apr 18 '22 at 15:47

score 0 · Answer 1 · answered Apr 21 '22 at 18:20

0

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

answered Apr 21 '22 at 18:20

Hogan

65,989
10
76
113

We're using SQL Server 2014 so this isn't available to us. Still doesn't answer my question on how to get a list to appear WITHIN my query above. – FSUKXAZ Apr 22 '22 at 18:16
@FSUKXAZ -- 9 years -- might be time to upgrade – Hogan Apr 27 '22 at 16:14

SQL Query Adding Another Table w/One To Many Relationship

1 Answers1