List:General Discussion« Previous MessageNext Message »
From:Travis Ard Date:September 7 2010 7:21pm
Subject:RE: Conditional join of tow tables
View as plain text  
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

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


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