Sql 2008 Sproc Help
I have a sproc which returns listings for dog breeders from my breeders table. A breeder can breed multiple dog breeds, so I have a seperate table for the breeds mapped by breeder id.
Ideally from my listings sproc I'd like to get back a list of breeds in a column alongside the rest of the breeder data, is there any way to do this or should I give in and make 2 calls then merge in C#?
Thanks in advance!
yes, a single join query can do it
Ok cool, thats good news. Any idea of the syntax?
can you give us the two table layouts?
those tables do not seem to be related
You will require three tables for a many-to-many relationship. A table for breeds, a table for breeders, and a third table with the primary key field from the aforementioned two tables.
Sorry, I don't have much experience with SQL, I'm teaching myself as I go. The two tables above are related through the DogBreederID field. This is the PK for the DogBreeders table and a foreign key for the DogBreeders_DogBreed. I also have a DogBreed table, but I did not think that mattered for the current issue, as DogBreed can be replaced with anything, I just need all applicable rows in the DogBreeders_DogBreed table combined into 1 column in my result
a simple join should do it
you might notice that it's pretty hard to read this query because of the proliferation of "dogbreed" throughout all the table and column names
ON DogBreeders_DogBreed.DogBreederID = DogBreeders.DogBreederID
a new naming strategy might be in order
Thanks for your help, but I guess I did not ask my question well enough. This returns multiple rows for the same breeder, I need the breeder to be distinct, and the breeds to be combined into one column, in a comma seperated list, or something comparable. Does this make sense? I'm not sure if SQL can do this, I might have to do it on the front end, but this is what I'm trying to figure out.
As far as the query being hard to read due to the "DogBreed" all over, I totally agree. I've developed it this way to make things easier to manage on the web end (I'm a web dev, not a SQL programmer), I suppose I should be using aliases right?
Thanks very much for your help, I appreciate it, I have a lot to learn about SQL
yes, you should do the concatenation in the front end, because it will keep the query simple
but if you really want to do it with SQL, see this technique -- Creating comma-delimited strings
Once the data you want has been found and returned, the database has done its job. Fiddling with the format is definitely a task best done in application land.
Use an order by clause on the breeder ID in SQL to make sure that the breeders are pseudo-grouped, then in your application it's quite easy to iterate over the rows, appending the breed to your comma-separated list if the breeder ID for the current row is the same as for the previous row.
Ok cool, thanks for all your help, very useful
Wasn't thinking about paging and sorting, that makes it so this kind of query doesnt work because it screws up all the counts.
What I ended up doing was adding a column in the DogBreeders table for DogBreedList, which stores the comma seperated list of breeds, then creating a trigger on the DogBreeders_DogBreed table, so each time its edited it updates the DogBreedList in DogBreeders. I know this is not ideal either, but there doesn't seem to be a perfect solution, and this data will not be edited often once I add it initially.