Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: Multiple relation tables and selections

    Hello !
    I'm trying to build a search engine that will search all words in any table that has a relation with my "t_programs" table.
    It is always an any-any relation. My programs have multiple writers, multiple themes, multiple cycle ...

    I tried this :

    SELECT
    DISTINCT(P.program_id) ,
    P.*
    FROM
    t_programs AS P
    , t_writers AS T0
    , r_programs_writers AS R0
    , t_themes AS T1
    , r_programs_themes AS R1
    , t_cycles AS T2
    , r_programs_cycles AS R2

    WHERE 1

    AND ( P.program_id = R0.fk_program_id AND R0.fk_writer_id = T0.writer_id )
    AND ( P.program_id = R1.fk_program_id AND R1.fk_theme_id = T1.theme_id )
    AND ( P.program_id = R2.fk_program_id AND R2.fk_cycle_id = T2.cycle_id )

    AND (


    T0.writer_label LIKE "%word1%"
    OR T1.theme_label LIKE "%word1%"
    OR T2.cycle_label LIKE "%word1%"

    ) AND (

    T0.writer_label LIKE "%word2%"
    OR T1.theme_label LIKE "%word2%"
    OR T2.cycle_label LIKE "%word2%"

    )

    I want the programs related to word1 AND word2. This request works fine, as soon as the programs have at least one relation with all the tables. But if the two words are in the t_themes.theme_label, and that the program has no cycle, it won't return. Is there a way to return to results two ? I'm not familiair with the JOIN syntax ...

    Thanx in advance !
    (and sorry for my english)


    Gavroche Le Gnou

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your english is fine
    Code:
    select distinct
         , P.program_id
      from t_programs as P 
    left outer
      join r_programs_writers as R0 
        on P.program_id = R0.fk_program_id
    left outer
      join t_writers as T0 
        on R0.fk_writer_id = T0.writer_id
    left outer
      join r_programs_themes as R1 
        on P.program_id = R1.fk_program_id
    left outer
      join t_themes as T1 
        on R1.fk_theme_id = T1.theme_id
    left outer
      join r_programs_cycles as R2 
        on P.program_id = R2.fk_program_id
    left outer
      join t_cycles as T2 
        on R2.fk_cycle_id = T2.cycle_id
     where ( 
           T0.writer_label like "%word1%" 
        or T1.theme_label like "%word1%" 
        or T2.cycle_label like "%word1%" 
           )
       and (
           T0.writer_label like "%word2%" 
        or T1.theme_label like "%word2%" 
        or T2.cycle_label like "%word2%" 
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    2

    THANKS !

    Yeah !!!
    Thanks a lot lot lot !
    It's working very fine, and returns exactly the results i wanted !

    I'll just try to optimize my tables a little more to see if i can make it faster (but it's already great, 1 second for 9 tables + relations and 3 words)
    Is there a way to make it even faster ? All i did was to set the primary keys and the foreign keys as INDEX ...

    Thanks a lot for your answer

    Gavroche Le ( happy )Gnou

Posting Permissions

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