#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    2
    Rep Power
    0

    sql query question


    I'm trying to get the right output for a query and I can't seem to do what I want to... wondering if I'm just missing something.

    I have 2 tables, for simplicity sake we will say they're like this

    Books that has columns of ISBN and Title
    Authors that has columns of ISBN and Authors

    Books has the following data:
    ISBN Title
    1234 Some Title

    Authors has the following data:
    ISBN Authors
    1234 John Smith
    1234 Bill Jones

    So when I join these 2 tables I get 2 rows, because my book "Some Title" has 2 authors. What i want to retrieve is 1 row that lists both authors like this:
    ISBN Title Authors
    1234 Some Title John Smith, Bill Jones

    Is there some way to do this w/ a simple query?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    with a simple query? no
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    2
    Rep Power
    0
    well then how about a not so simple query? a push in the right direction would be more than helpful.


    would it be any easier in oracle? the tables originate in oracle but somewhere in the process get transfered to mssql.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,235
    Rep Power
    4279
    if you can write a stored procedure, use a cursor to walk the result set

    http://sqlteam.com/item.asp?ItemID=2368

    rudy

IMN logo majestic logo threadwatch logo seochat tools logo