Multiple relation tables and selections

GavrocheLeGnou · #1 (**permalink**) 02-09-04, 05:25

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

r937 · #2 (**permalink**) 02-09-04, 09:03

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%" 
       )

GavrocheLeGnou · #3 (**permalink**) 02-09-04, 09:41

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

Internet Services

Web Development

Digital Marketing

Consumer Tech