SitePoint Sponsor |
|
User Tag List
Results 1 to 14 of 14
Thread: Sql 2008 Sproc Help
-
Oct 2, 2009, 14:55 #1
- Join Date
- Jul 2009
- Location
- Orange, CA
- Posts
- 20
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
-
Oct 3, 2009, 17:03 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
yes, a single join query can do it
-
Oct 3, 2009, 19:31 #3
- Join Date
- Jul 2009
- Location
- Orange, CA
- Posts
- 20
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Ok cool, thats good news. Any idea of the syntax?
-
Oct 3, 2009, 23:08 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
sure
can you give us the two table layouts?
-
Oct 4, 2009, 21:47 #5
- Join Date
- Jul 2009
- Location
- Orange, CA
- Posts
- 20
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
-
Oct 5, 2009, 02:39 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
those tables do not seem to be related
-
Oct 5, 2009, 03:57 #7
- Join Date
- Aug 2006
- Location
- Samsara
- Posts
- 451
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Oct 5, 2009, 10:26 #8
- Join Date
- Jul 2009
- Location
- Orange, CA
- Posts
- 20
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Oct 5, 2009, 10:54 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
a simple join should do it
Code:SELECT DogBreeders.DogBreederID , DogBreeders.DogBreederName , DogBreeders_DogBreed.DogBreed FROM DogBreeders LEFT OUTER JOIN DogBreeders_DogBreed ON DogBreeders_DogBreed.DogBreederID = DogBreeders.DogBreederID
a new naming strategy might be in order
-
Oct 5, 2009, 11:22 #10
- Join Date
- Jul 2009
- Location
- Orange, CA
- Posts
- 20
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Oct 5, 2009, 11:25 #11
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Oct 5, 2009, 20:02 #12
- Join Date
- Aug 2006
- Location
- Samsara
- Posts
- 451
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Oct 6, 2009, 01:44 #13
- Join Date
- Jul 2009
- Location
- Orange, CA
- Posts
- 20
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Ok cool, thanks for all your help, very useful
-
Oct 7, 2009, 14:03 #14
- Join Date
- Jul 2009
- Location
- Orange, CA
- Posts
- 20
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Bookmarks