mysql complex Join

Question

i have a select statement combined from 7 tables

SELECT * (//instead of * i put in my code the proper columns i need from the selected tabls)
FROM 
ev_events_eve
LEFT JOIN ev_events_multilang_evmulti ON ev_events_multilang_evmulti.event_evmulti = ev_events_eve.id_eve
LEFT JOIN ev_schedule_sch ON ev_schedule_sch.event_id_sch = ev_events_eve.id_eve
LEFT JOIN ev_events_type_et ON ev_schedule_sch.event_type_id_sch = ev_events_type_et.id_et
LEFT JOIN ev_events_type_multilang_etmulti ON ev_events_type_multilang_etmulti.idevent_type_etmulti = ev_events_type_et.id_et
LEFT JOIN ev_schedule_multilang_schmulti ON ev_schedule_multilang_schmulti.id_schedule_schmulti = ev_schedule_sch.id_sch
LEFT JOIN ev_timetable_tt ON ev_timetable_tt.schedule_id_tt = ev_schedule_sch.id_sch AND ev_timetable_tt.event_id_tt = ev_events_eve.id_eve
WHERE (ev_events_eve.active_eve = 1 AND ev_schedule_multilang_schmulti.iso_code_lang_schmulti = '{$current_language}' AND ev_schedule_sch.active_sch = 1) AND
ev_timetable_tt.end_date_tt > CURDATE() OR (ev_timetable_tt.weekday_tt = DAYNAME(CURDATE()) AND ev_timetable_tt.end_date_tt > CURDATE()) OR ev_timetable_tt.specific_date_tt > CURDATE()
ORDER BY ev_timetable_tt.start_date_tt ASC LIMIT 3

the problem that this statement ignoring the where clause, every time i use mysql i used to create sample sql statement consists of two or three tables with a normal left or inner join, i need someone to discuss this issue with me, it may let me have a further look to something i don't mentioned in my code, however i know that the above code is not enough to let anyone handle this problem with me, but i need for someone who can discuss it with me

I updated the question with the following information

The main business rule:

• The project is for Multilanguage website
  
  
• the tables are:
  • ev_events_eve (events entity like id, logo, slogan, start and end dates for whole the event)
    • ev_events_multilang_evmulti (Multilanguage table for the main event table like name (en, fr, gr .. etc), description … etc )
    • ev_schedule_sch (schedule entity like id, foreign key to event table)
    • ev_schedule_multilang_schmulti (Multilanguage table for the main schedule table)
    • ev_timetable_tt (store the start and end dates & times related to ev_schedule_sch table)
      
      
• every table is duplicated into two tables, one of them to store the entity and the other one to store the Multilanguage data
  
  
• relation between tables
  • main table is ev_events_eve
  • ev_events_multilang_evmulti is related to the main table via foreign key
  • ev_schedule_sch related to the main table via foreign key
  • ev_schedule_multilang_schmulti related to ev_schedule_sch table via foreign key
  • ev_timetable_tt is related to ev_schedule_sch via foreign key

I need to write a statement to retrieve the incoming 3 rows from events table (let’s say that I need to retrieve the latest 3 rows as you don’t have the full schema for all fields, and later we can discuss in the WHERE clause :) ) and plus this 3 rows also retrieve 6 rows from schedule table related to every row in the events table So the estimated result must be like that Assume I have 4 events

• 1st is called : event#1
  • This event have 3 rows in the schedule related tables (so retrieve
    all these rows)
    
    
• 2nd is called: event#2
  • This have 11 rows in the schedule related tables (retrieve only 6 rows from them)
    
    
• 3rd is called: event#3
  • This event doesn’t have any rows related to it in the schedule related tables
    
    
• 4th is called: event#4
  • This event have only 1 row related to it in the schedule related tables

Only I need to retrieve the first 3 events and the related in the schedule as described above

Answer 1

First, I too believe your date criteria in the where clause should be wrapped to encapsulate it all. If not, its applying the first part, then completely ignoring it as soon as the first "OR" is qualified. Next. You have many LEFT JOINs which implies as long as the record on the left side is qualified, only OPTIONAL for the record in the RIGHT side table. BUT, once you add criteria to your WHERE clause against said right-side table, it throws out the "LEFT" qualification. You should move that criteria to the LEFT JOIN condition instead of in the where clause (as I have adjusted).

Your original query slightly cleaned up, using aliases and correcting the parenthesis for date criteria so you dont get false results, but shown as implied via the WHERE clause turning it into NORMAL INNER join.

SELECT *
   FROM 
      ev_events_eve EE

         LEFT JOIN ev_events_multilang_evmulti ELANG 
            ON EE.id_eve = ELANG.event_evmulti

         LEFT JOIN ev_schedule_sch SCHED 
            ON EE.id_eve = SCHED.event_id_sch

            LEFT JOIN ev_events_type_et ETYPE
               ON SCHED.event_type_id_sch = ETYPE.id_et

               LEFT JOIN ev_events_type_multilang_etmulti ETMLANG 
                  ON ETYPE.id_et = ETMLANG.idevent_type_etmulti

            LEFT JOIN ev_schedule_multilang_schmulti SMS 
               ON SCHED.id_sch = SMS.id_schedule_schmulti

            LEFT JOIN ev_timetable_tt TIMET 
               ON SCHED.id_sch  = TIMET.schedule_id_tt
              AND EE.id_eve = TIMET.event_id_tt

   WHERE 
          (   EE.active_eve = 1 
          AND SMS.iso_code_lang_schmulti = '{$current_language}' 
          AND SCHED.active_sch = 1 ) 
      AND (      TIMET.end_date_tt > CURDATE() 
            OR (    TIMET.weekday_tt = DAYNAME(CURDATE()) 
                AND TIMET.end_date_tt > CURDATE()) 
            OR TIMET.specific_date_tt > CURDATE() )
   ORDER BY 
      TIMET.start_date_tt ASC LIMIT 3

And here's the query adjusted to keep respective LEFT JOINs, notice the differences ON where your criteria are placed.

SELECT *
   FROM 
      ev_events_eve EE

         LEFT JOIN ev_events_multilang_evmulti ELANG 
            ON EE.id_eve = ELANG.event_evmulti

         LEFT JOIN ev_schedule_sch SCHED 
            ON EE.id_eve = SCHED.event_id_sch
           AND SCHED.active_sch = 1

            LEFT JOIN ev_events_type_et ETYPE
               ON SCHED.event_type_id_sch = ETYPE.id_et

               LEFT JOIN ev_events_type_multilang_etmulti ETMLANG 
                  ON ETYPE.id_et = ETMLANG.idevent_type_etmulti

            LEFT JOIN ev_schedule_multilang_schmulti SMS 
               ON SCHED.id_sch = SMS.id_schedule_schmulti
              AND SMS.iso_code_lang_schmulti = '{$current_language}' 

            LEFT JOIN ev_timetable_tt TIMET 
               ON SCHED.id_sch  = TIMET.schedule_id_tt
              AND EE.id_eve = TIMET.event_id_tt
              AND (      TIMET.end_date_tt > CURDATE() 
                    OR (    TIMET.weekday_tt = DAYNAME( CURDATE() ) 
                        AND TIMET.end_date_tt > CURDATE() ) 
                    OR TIMET.specific_date_tt > CURDATE()    )
   WHERE 
      EE.active_eve = 1 
   ORDER BY 
      TIMET.start_date_tt ASC LIMIT 3

One final note. Your ORDER BY is referencing the "ev_TimeTable_tt" (aliased as TIMET) table which to me would imply that you ALWAYS are expecting only matches to EXPLICITLY be found in this table. If so, this should in-fact be an INNER JOIN and not a LEFT JOIN.

Since getting to the "EV_TIMETABLE_TT" table requires a find from the schedule table, I would expect this too is an INNER JOIN requirement.

So, with the clarifications of LEFT vs INNER join you probably need to adjust to a combination of these. Not knowing your data, I can't really direct any further.

Answer 2

Using LEFT JOINs in conjunction with WHERE conditions which require a non-NULL value in those tables which are LEFT JOINed defeats the very purpose of using a LEFT JOIN. You either have to specify in the WHERE conditions that the value can be [whatever] OR NULL or make the WHERE condition part of the ON of the JOIN.

For example:

... WHERE ... (ev_schedule_sch.active_sch = 1 OR ev_schedule_sch.active_sch IS NULL) ...

Or

... LEFT JOIN ev_schedule_sch ON ev_schedule_sch.event_id_sch = ev_events_eve.id_eve AND ev_schedule_sch.active_sch = 1 ...

Answer 3

I am just guessing the problem is with the query's OR parts. I just changed this part in query:

AND ( 
ev_timetable_tt.end_date_tt > CURDATE() 
OR (ev_timetable_tt.weekday_tt = DAYNAME(CURDATE()) AND ev_timetable_tt.end_date_tt > CURDATE()) 
OR ev_timetable_tt.specific_date_tt > CURDATE()
)

Probably you want to do this: (I gave alias to make it more readable)

SELECT * 
FROM ev_events_eve e
LEFT JOIN ev_events_multilang_evmulti eml ON eml.event_evmulti = e.id_eve
LEFT JOIN ev_schedule_sch es ON es.event_id_sch = e.id_eve
LEFT JOIN ev_events_type_et et ON es.event_type_id_sch = et.id_et
LEFT JOIN ev_events_type_multilang_etmulti emel ON emel.idevent_type_etmulti = et.id_et
LEFT JOIN ev_schedule_multilang_schmulti ems ON ems.id_schedule_schmulti = es.id_sch
LEFT JOIN ev_timetable_tt ett ON ett.schedule_id_tt = es.id_sch AND ett.event_id_tt = e.id_eve
WHERE (e.active_eve = 1 AND ems.iso_code_lang_schmulti = '{$current_language}' AND es.active_sch = 1) 
AND 
(
    ett.end_date_tt > CURDATE() 
OR (ett.weekday_tt = DAYNAME(CURDATE()) AND ett.end_date_tt > CURDATE()) 
OR ett.specific_date_tt > CURDATE()
)
ORDER BY ett.start_date_tt ASC
LIMIT 3

Of if you prefer in your syntax (without table alias):

SELECT * (//instead of * i put in my code the proper columns i need from the selected tabls)
FROM 
ev_events_eve
LEFT JOIN ev_events_multilang_evmulti ON ev_events_multilang_evmulti.event_evmulti = ev_events_eve.id_eve
LEFT JOIN ev_schedule_sch ON ev_schedule_sch.event_id_sch = ev_events_eve.id_eve
LEFT JOIN ev_events_type_et ON ev_schedule_sch.event_type_id_sch = ev_events_type_et.id_et
LEFT JOIN ev_events_type_multilang_etmulti ON ev_events_type_multilang_etmulti.idevent_type_etmulti = ev_events_type_et.id_et
LEFT JOIN ev_schedule_multilang_schmulti ON ev_schedule_multilang_schmulti.id_schedule_schmulti = ev_schedule_sch.id_sch
LEFT JOIN ev_timetable_tt ON ev_timetable_tt.schedule_id_tt = ev_schedule_sch.id_sch AND ev_timetable_tt.event_id_tt = ev_events_eve.id_eve
WHERE (ev_events_eve.active_eve = 1 AND ev_schedule_multilang_schmulti.iso_code_lang_schmulti = '{$current_language}' AND ev_schedule_sch.active_sch = 1) 
AND ( 
ev_timetable_tt.end_date_tt > CURDATE() 
OR (ev_timetable_tt.weekday_tt = DAYNAME(CURDATE()) AND ev_timetable_tt.end_date_tt > CURDATE()) 
OR ev_timetable_tt.specific_date_tt > CURDATE()
)
ORDER BY ev_timetable_tt.start_date_tt ASC LIMIT 3