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:mysql@stripped]
> Sent: Tuesday, September 07, 2010 1:43 AM
> To: mysql@stripped
> 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
>
