SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Hybrid View

  1. #1
    SitePoint Member
    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!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, a single join query can do it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    sure

    can you give us the two table layouts?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    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!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    those tables do not seem to be related
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePοint Troll disgracian's Avatar
    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.

  8. #8
    SitePoint Member
    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

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    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
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    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

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePοint Troll disgracian's Avatar
    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.

  13. #13
    SitePoint Member
    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

  14. #14
    SitePoint Member
    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.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •