Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: 1:M single line return

    Just messing about with a silly little problem in SQL which has me stumped!
    The problem goes like this;
    There are lots of books.
    There are lots of authors.
    Every book has atleast one author (primary author).
    Some books have a secondary author too!

    Books(BookId, BookName)
    Authors(AuthorId, AuthorName)
    BookAuth(BookId, AuthorId, PrimaryAuth)
    Where primary auth is a bit field; 1 denotes primary, 0 denotes secondary.

    The problem is trying to return a single line per book - something like
    Code:
    BookName	Primary Author		Secondary Author
    Book 1		Dave			NULL
    Book 2		John			Dave
    Book 3		Laura			John
    The following code is the little test script I've written which you may wish to utilise
    Code:
    IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'Books') BEGIN
    	DROP TABLE Books
    END
    IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'Authors') BEGIN
    	DROP TABLE Authors
    END
    IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'BookAuth') BEGIN
    	DROP TABLE BookAuth
    END
    
    CREATE TABLE Books (bookid int, bookname varchar(50))
    CREATE TABLE Authors (authorid int, authorname varchar(50) )
    CREATE TABLE BookAuth (authorid int, bookid int, primaryauth bit)
    GO
    
    INSERT INTO Books
    SELECT 1, 'MS Access Book'		UNION ALL
    SELECT 2, 'Microsoft Excel for Peanuts' UNION ALL
    SELECT 3, 'Some Autobiography'		UNION ALL
    SELECT 4, 'SQL for Beginners'		UNION ALL
    SELECT 5, 'Advanced T-SQL'		UNION ALL
    SELECT 6, 'I can''t think of another title'
    
    INSERT INTO Authors
    SELECT 1, 'Dave'	UNION ALL
    SELECT 2, 'Steve'	UNION ALL
    SELECT 3, 'John'	UNION ALL
    SELECT 4, 'Joanne'	UNION ALL
    SELECT 5, 'Christina'	UNION ALL
    SELECT 6, 'Alex'
    
    INSERT INTO BookAuth
    SELECT 1,1,1 UNION ALL
    SELECT 2,1,0 UNION ALL
    SELECT 1,2,1 UNION ALL
    SELECT 1,3,1 UNION ALL
    SELECT 3,3,0 UNION ALL
    SELECT 4,4,1 UNION ALL
    SELECT 4,5,1 UNION ALL
    SELECT 5,6,1 UNION ALL
    SELECT 6,6,0
    
    SELECT * FROM bookauth
    
    DROP TABLE Books
    DROP TABLE Authors
    DROP TABLE BookAuth
    It should be so easy - but I can't get it so far!
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Defo no Tertiary authors?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    None .
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sounds like homework to me.....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You know me better than that!
    It's just a problem I came across that intrigued me and so far has beaten me!

    It sounded so simple to me at first but I jsut can't do it!
    I can't leave it until I have a solution
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT books.bookname
      , MAX(CASE WHEN bookauth.primaryauth = 1 THEN authors.authorname END) AS primary_author
      , MAX(CASE WHEN bookauth.primaryauth = 0 THEN authors.authorname END) AS secondary_author
    FROM dbo.books
    LEFT OUTER JOIN 
      dbo.bookauth
    ON bookauth.bookid = books.bookid
    LEFT OUTER JOIN 
      dbo.Authors
    ON authors.authorid = bookauth.authorid
    GROUP BY books.bookname
    The key question on this sort of thing (this is essentially a pivot) is "Are there a finite number of values to pivot on?". If yes and it is a sensible number then you can use case. If no then you need to use dynamic SQL or (even better) get ther front end to handle it.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Aha!
    So simple.
    I've written a number of queries just like this in the past - just didn't cotton on that this problem would require a pivot.

    Cheers Poots - I can finally put this one to rest
    George
    Home | Blog

Posting Permissions

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