Results 1 to 4 of 4

Thread: Left Join?

  1. #1
    Join Date
    Dec 2011
    Posts
    1

    Question Unanswered: Left Join?

    I'm doing some web/database work for a family friend's school music book collection and my limited SQL knowledge has hit a brick wall...

    I've got a table of Books (Book_ID, Title) and a table of Songs from the books (Song_ID, Book_ID, Name).
    I'm trying to create a query that will list all books with a given word in their title, and all songs that contain that word with the book they're from (regardless of whether the book's title contains the word).

    I think I need to do a LEFT JOIN on the two tables (by the Book_ID field), but I'm unsure how to combine the word filters with it. Is it possible to do a SELECT on either table before the JOIN? So I can do something like...
    SELECT Book_ID, Title FROM
    (SELECT Book_ID, Title FROM Books WHERE Title LIKE '%word%') as FilteredBooks
    LEFT JOIN
    (SELECT Book_ID, Name FROM Songs WHERE Name LIKE '%word%') as FilteredSongs
    ON FilteredBooks.Book_ID=FilteredSongs.Book_ID;

    Cheers!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sounds more like you want UNION, not LEFT JOIN
    Code:
    SELECT 'book' AS result_type
         , Book_ID
         , Title 
         , NULL as Name
      FROM Books 
     WHERE Title LIKE '%word%'
    UNION ALL
    SELECT 'song' AS result_type
         , Book_ID
         , Title 
         , Name 
      FROM Songs 
     WHERE Name LIKE '%word%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The result of UNION may include *extra* Book row(a row without Song, additional to rows with Song) for a Book,
    if the Book include given "word".

    Sample/test data:
    Code:
    SELECT * FROM Books;
    ------------------------------------------------------------------------------
    
    BOOK_ID     TITLE            DESCRIPTIONS                  
    ----------- ---------------- ------------------------------
              1 Book A           One Song   with         "word"
              2 Book B           No Song                       
              3 Book C           Some Songs with/without "word"
              4 Book is a word   No Song                       
              5 words in Book E  Two Songs  with         "word"
              7 Book G: for word Some Songs      without "word"
    
      6 record(s) selected.
    
    
    SELECT * FROM Songs;
    ------------------------------------------------------------------------------
    
    SONG_ID     BOOK_ID     NAME                     
    ----------- ----------- -------------------------
            100           1 A word from Song 100     
            200           3 Song 200 word & words    
            300           3 Song 300 has a word in it
            400           3 Song 400 work            
            500           3 Song 500 has some words  
            600           5 Song 600 has a word      
            700           5 Song 700 has words       
            800           7 Song 800                 
            900           7 Song 900                 
    
      9 record(s) selected.

    Example 1: UNION ALL
    Code:
    SELECT Book_ID , Title
         , NULLIF(0  , 0 ) AS Song_ID
         , NULLIF('' , '') AS Name
         , 'B  '           AS B_S
     FROM  Books 
     WHERE Title LIKE '%word%'
    UNION ALL
    SELECT b.Book_ID , b.Title 
         , s.Song_ID
         , s.Name
         , '  S'           AS B_S
     FROM  Songs s
     INNER JOIN
           Books b
       ON  b.Book_ID = s.Book_ID
     WHERE s.Name  LIKE '%word%'
     ORDER BY
           Book_ID
         , B_S     DESC
         , Song_ID
    ;
    ------------------------------------------------------------------------------
    
    BOOK_ID     TITLE            SONG_ID     NAME                      B_S
    ----------- ---------------- ----------- ------------------------- ---
              1 Book A                   100 A word from Song 100        S
              3 Book C                   200 Song 200 word & words       S
              3 Book C                   300 Song 300 has a word in it   S
              3 Book C                   500 Song 500 has some words     S
              4 Book is a word             - -                         B  
              5 words in Book E            - -                         B     <--- Book_ID = 5, without Song
              5 words in Book E          600 Song 600 has a word         S   <--- Book_ID = 5, with Song
              5 words in Book E          700 Song 700 has words          S   <--- Book_ID = 5, with Song
              7 Book G: for word           - -                         B  
    
      9 record(s) selected.

    Example 2: LEFT OUTER JOIN
    Code:
    SELECT b.Book_ID
         , b.Title 
         , s.Song_ID
         , s.Name
         , CASE WHEN b.Title LIKE '%word%' THEN  'B' ELSE  ' ' END
           ||
           CASE WHEN s.Name  LIKE '%word%' THEN ' S' ELSE '  ' END
           AS B_S
     FROM  Books b
     LEFT  OUTER JOIN
           Songs s
       ON  s.Book_ID = b.Book_ID
       AND s.Name    LIKE '%word%'
     WHERE b.Title   LIKE '%word%'
       OR  s.Song_ID IS NOT NULL
     ORDER BY
           Book_ID
         , Song_ID
    ;
    ------------------------------------------------------------------------------
    
    BOOK_ID     TITLE            SONG_ID     NAME                      B_S
    ----------- ---------------- ----------- ------------------------- ---
              1 Book A                   100 A word from Song 100        S
              3 Book C                   200 Song 200 word & words       S
              3 Book C                   300 Song 300 has a word in it   S
              3 Book C                   500 Song 500 has some words     S
              4 Book is a word             - -                         B  
              5 words in Book E          600 Song 600 has a word       B S   <--- Book_ID = 5, with Song
              5 words in Book E          700 Song 700 has words        B S   <--- Book_ID = 5, with Song
              7 Book G: for word           - -                         B  
    
      8 record(s) selected.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Tested on Mimer SQL Developers - Mimer SQL-2003 Validator

    Both of Example 1 and Example 2 were...
    Code:
    Result:
    
    Conforms to Core SQL-2003
    
    The following vendor reserved word is used:
    
    NAME

Posting Permissions

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