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=gcdmg-mysql-2@m.gmane.org

RE: Conditional join of tow tables

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@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=travis_ard@hotmail.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Conditional join of tow tables

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@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=gcdmg-mysql-2@m.gmane.org