Sql 2008 Sproc Help

[PetStew]

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!

[r937]

yes, a single join query can do it

[PetStew]

Ok cool, thats good news. Any idea of the syntax?

[r937]

sure

can you give us the two table layouts?

[PetStew]

DogBreeders_DogBreed
DogBreederID int
DogBreed varchar(50)

DogBreeders
DogBreederID int
DogBreederName varchar(200)
Contact varchar(50)
Address1 varchar(200)
Address2 varchar(200)
CityName varchar(50)
StateName varchar(50)
ZipCode bigint
Phone bigint
Fax bigint
Url varchar(100)
Email varchar(100)
Description varchar(MAX)
AverageRating float

Thanks!

[r937]

those tables do not seem to be related

[disgracian]

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.

Cheers,
D.

[PetStew]

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

[r937]

a simple join should do it

SELECT DogBreeders.DogBreederID
     , DogBreeders.DogBreederName
     , DogBreeders_DogBreed.DogBreed
  FROM DogBreeders
LEFT OUTER
  JOIN DogBreeders_DogBreed
    ON DogBreeders_DogBreed.DogBreederID = DogBreeders.DogBreederID

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

a new naming strategy might be in order

[PetStew]

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

[r937]

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

[disgracian]

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.

Cheers,
D.

[PetStew]

Ok cool, thanks for all your help, very useful

[PetStew]

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.