SQL Server: Select Statement that pulls Distinct from table A and shows delimited values from TableB (Many-to-many relationship)

Question

Not sure how to pull data from BND_Listing with a SELECT & JOIN on my tables listed below so that it dosen't duplicate a record in my BND_Listing table for each time it's listed on my BND_ListingJunction table.

My Tables:
BND_Listing = contains unique store data (ID=LID)
BND_ListingCategories = contains a list of categories (ID=CatID)
BND_ListingJunction = contains relations between (LID & CatID)

Ideally i'd like to SELECT * FROM BND_Listing and display on each unique LID another column with delimited CatIDs for each time that LID is listed on my BND_ListingJunction table.

My query so far:

SELECT DISTINCT l.*, c.CategoryName
FROM BND_Listing_testing l
   Join BND_ListingJunction_testing j
      on j.Junc_LID = l.LID
   join BND_ListingCategories c
      on c.CatId = j.Junc_CatID
Where (c.CategoryName = '[querystring:filter-Category]' or
                    '[querystring:filter-Category]'='All')
      and (City = '[querystring:filter-City]' or 
                    '[querystring:filter-City]'='All')
      and (Region= '[querystring:filter-State]' or 
                    '[querystring:filter-State]'='All')
      and (Country= '[querystring:filter-Country]' or 
                    '[querystring:filter-Country]'='All')
      and isnull(Company,'') <> ''
Order by l.Company, l.LID

UPDATE from Matt's answer: ( Giving me incorrect syntax errors )

SELECT l.LID, l.Company, j.CatID = 
    STUFF((SELECT CONVERT(varchar(100), CatID) + ', '
           FROM BND_ListingJunction_testing j
           WHERE j.Junc_LID = l.LID
           FOR XML PATH('')), 1, 2, '')
FROM BND_Listing_testing l
GROUP BY LID

UPDATE for Pacreely:

My desired results would be:

LID  Company Doors  Address           CatID
1    a       a      123 my address    1
2    b       b      234 my address    1,2
3    c       c      456 my address    37,40,28,30

can you provide an example of your desired result-set? Sounds like your solution is to PIVOT with DYNAMIC SQL. — pacreely, Dec 05 '16 at 14:57

score 2 · Accepted Answer · edited May 23 '17 at 10:30

2

XPATH is one way if you want a single column of data. You would need to JOIN to the subquery rather than directly to the table. Example here from a similar question.

SQL Server : GROUP BY clause to get comma-separated values

Doing PIVOT would be very complex given the one to many relationship and the need for Dynamic SQL to handle the one to many relationship. This will also return you more than one column.

edited May 23 '17 at 10:30

Community

1
1

answered Dec 05 '16 at 15:21

Matt Fischer

76
1

I've tried piecing together some xpath into the query but am getting errors. I've added it to the question. – UserSN Dec 05 '16 at 18:14
1

XML PATH is the best solution for this, keep tweaking your code, it's almost there. – pacreely Dec 05 '16 at 18:44
@pacreely anything in particular you've spotted in the code? – UserSN Dec 05 '16 at 18:54

score 1 · Answer 2 · answered Dec 05 '16 at 19:19

1

On your first line replace the "j.stardate = " with just "startdate ="

answered Dec 05 '16 at 19:19

pacreely

1,881
2
10
16

How can I add `Where (Categories = '[querystring:filter-Category]' or '[querystring:filter-Category]'='All')` to the query? I put it after the `FROM` but am getting invalid column name? – UserSN Dec 05 '16 at 19:55
you probably want to use the LIKE command to interrogate your Category ID string. – pacreely Dec 06 '16 at 09:26

SQL Server: Select Statement that pulls Distinct from table A and shows delimited values from TableB (Many-to-many relationship)

2 Answers2