Menu
I've got a table with a field for Merchant's name and field with the Services they provide. The Services field is a comma separated list of integers that relate to another Services table, with the Service id and the Service Name fields.
I'm trying to create a single query that joins those two, so I can have a list of Merchants, along with the Services Names. My solution so far has been to do a second loop within my initial 'foreach' loop, but that can mean 5 or 6 db calls for each Merchant name.
After some StackOverflow-ing (google-ing), I noticed that using a comma separated field is probably not the best way to go.
Anyone have either a way to do the join, or thoughts on how the db structure could be set up better? Many thanks in advance!
637
description FROM notes n LEFT JOIN Positions p ON p.id = n. forDepts LEFT JOIN companies c ON c. userid = n. clientId LEFT JOIN companies c2 ON c2.
There is no difference at all. There is absolutely no difference in performance. Final version prepared by SQL Server would be the same in both the cases. For larger queries I would say that the first way is more readable.
You never store comma separated arrays in a database - each entry in the comma separated array needs to be stored in its own row in a table in the database.
In order to fetch the comma separated (delimited) values from the Stored Procedure, you need to make use of a variable with data type and size same as the Output parameter and pass it as Output parameter using OUTPUT keyword.
The short term solution to your issue is to use the FIND_IN_SET function to join the MERCHANT and SERVICES tables:
SELECT *
FROM MERCHANT m
JOIN SERVICES s ON FIND_IN_SET(s.service_id, m.services) > 0
The long term solution is to correct your tables - never allow columns to contain comma separated lists of referential ID/etc values.
142
answered Sep 18 '22 22:09
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With