CODEWITHSUNDEEP
mysqlsql
Rainer
Rainer

Reputation: 1

Using multiple JOINs with several conditions

I try to use several helper tables to display the status of a timer. (Browser with php) The helper tables are used to be able to switch between languages.

Here the helper table for Timer Types:

+-------------+------+------------------+
| lfdLanguage | Type | TText            |
+-------------+------+------------------+
|           0 |    0 | Puls             |
|           0 |    1 | steigende Flanke |
|           0 |    2 | fallende Flanke  |
|           1 |    0 | Pulse            |
|           1 |    1 | rissing edge     |
|           1 |    2 | falling edge     |
+-------------+------+------------------+

and for Day Active:

+-------------+--------+--------+
| lfdLanguage | Active | ACText |
+-------------+--------+--------+
|           0 |      0 | _      |
|           0 |      1 | X      |
|           1 |      0 | _      |
|           1 |      1 | X      |
+-------------+--------+--------+

Usually I use equi joins, but here I would like to display the status of the days (i.e. RunMonday, ...) using the same helper table.

Using the following statement without the AND WHERE HTT.lfdLanguage = 0 works fine.

But with the where statement I can't exexute. I get an error:

('ERROR 1064 (42000): .....')

SELECT DISTINCT
  T.lfdTimer,
  T.Active,
  T.Name,
  T.Type,         HTT.TText,
  T.RunMonday,    HTDA1.ACText,
  T.RunTuesday,   HTDA2.ACText,
  T.RunWednesady, HTDA3.ACText,
  T.RunThursday,  HTDA4.ACText,
  T.RunFriday,    HTDA5.ACText,
  T.RunSaturday,  HTDA6.ACText,
  T.RunSunday,    HTDA7.ACText
FROM
  Timer T
INNER JOIN HelpTimerType      HTT   ON (T.Type         = HTT.Type     AND  WHERE HTT.lfdLanguage = 0);
 LEFT JOIN HelpTimerDayActive HTDA1 ON (T.RunMonday    = HTDA1.Active)
 LEFT JOIN HelpTimerDayActive HTDA2 ON (T.RunTuesday   = HTDA2.Active)
 LEFT JOIN HelpTimerDayActive HTDA3 ON (T.RunWednesady = HTDA3.Active)
 LEFT JOIN HelpTimerDayActive HTDA4 ON (T.RunThursday  = HTDA4.Active)
 LEFT JOIN HelpTimerDayActive HTDA5 ON (T.RunFriday    = HTDA5.Active)
 LEFT JOIN HelpTimerDayActive HTDA6 ON (T.RunSaturday  = HTDA6.Active)
 LEFT JOIN HelpTimerDayActive HTDA7 ON (T.RunSunday    = HTDA7.Active) 
;

Any idea how to include the language selection into the JOINs?

All help appreciated. Thanks

Upvotes: 0

Views: 45

Answers (2)

isaace
isaace

Reputation: 3429

Remove the 

WHERE

from your inner join, this is wrong syntax.

Upvotes: 0

Shadow
Shadow

Reputation: 34231

The where clause comes after all the joins:

SELECT DISTINCT
  T.lfdTimer,
  T.Active,
  T.Name,
  T.Type,         HTT.TText,
  T.RunMonday,    HTDA1.ACText,
  T.RunTuesday,   HTDA2.ACText,
  T.RunWednesady, HTDA3.ACText,
  T.RunThursday,  HTDA4.ACText,
  T.RunFriday,    HTDA5.ACText,
  T.RunSaturday,  HTDA6.ACText,
  T.RunSunday,    HTDA7.ACText
FROM
  Timer T
INNER JOIN HelpTimerType      HTT   ON (T.Type         = HTT.Type)
 LEFT JOIN HelpTimerDayActive HTDA1 ON (T.RunMonday    = HTDA1.Active)
 LEFT JOIN HelpTimerDayActive HTDA2 ON (T.RunTuesday   = HTDA2.Active)
 LEFT JOIN HelpTimerDayActive HTDA3 ON (T.RunWednesady = HTDA3.Active)
 LEFT JOIN HelpTimerDayActive HTDA4 ON (T.RunThursday  = HTDA4.Active)
 LEFT JOIN HelpTimerDayActive HTDA5 ON (T.RunFriday    = HTDA5.Active)
 LEFT JOIN HelpTimerDayActive HTDA6 ON (T.RunSaturday  = HTDA6.Active)
 LEFT JOIN HelpTimerDayActive HTDA7 ON (T.RunSunday    = HTDA7.Active)
 WHERE HTT.lfdLanguage = 0
;

Upvotes: 1

Related Questions