List:General Discussion« Previous MessageNext Message »
From:mysql Date:September 8 2010 4:54am
Subject:Re: Conditional join of tow tables
View as plain text  
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
>

Thread
Conditional join of tow tablesmysql7 Sep
  • RE: Conditional join of tow tablesTravis Ard7 Sep
    • Re: Conditional join of tow tablesmysql8 Sep