Hi Travis Thank you for the hint. i yesterday found the following hint, which I then followed.
select t1.datum, t2.sdat. t3.tag from table as t1 left outer join table2 as t2 on t1.datum = t2.sdat left outer join table2 as t3 on dayname(t1.datum) = t3.tag .... Note: it does not work with inner joins, you have to take outer joins. suomi On 2010-09-07 21:21, Travis Ard wrote:
Does this work? select * from t1 join t2 on (t1.datum = t2.sdat or dayname(t1.datum) = t2.tag); -Travis -----Original Message----- From: mysql [mailto:my...@ayni.com] Sent: Tuesday, September 07, 2010 1:43 AM To: mysql@lists.mysql.com Subject: Conditional join of tow tables Hi listers mysql> show global variables like "version"; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 5.1.46 | +---------------+--------+ 1 row in set (0.02 sec) mysql> Following problem: Two tables which must be joined differently depending on the contents of the second table, the first table esentially contains a date field named datum. the second table is as follows: mysql> describe schulung; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | sdat | date | YES | | NULL | | | tag | text | YES | MUL | NULL | | | szeit | time | YES | | NULL | | | speziell | text | YES | | NULL | | | id | int(10) unsigned | NO | PRI | NULL | auto_increment | +----------+------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> Now, if the second table in the sdat field contains a value which is equivalent to the datum field in the first table (datum = sdat), then this join must be taken and nothing else. Otherwise the more general join via the tag field must be taken (dayname(datum) = tag). I tried to program this using not exists in the on clause of a join .... inner join schulung on (if not exists (select sdat from schulung where sdat = datum) then (datum = sdat)) else dayname(datum) = tag).... but I got an ERROR 1064 near 'not exists (select sdat ....' Probably, I have to re-structure the entire statement to an other form using other constructs? Has anyone had similar problems? How did you solve it then? Thank you very much. suomi
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
