I just answered my own question with tinkering around with the sql
statement.
The solution is:
select
t2.*,
t3.name
from
table1 t1,
table2 t2
left outer join table3 t3
on (
t3.prid = substring_index(t2.weird, '-', 1)
and
t3.cid = substring_index(t2.weird, '-', -1)
)
where
t1.key = 1111
and
t1.id = t2.id;
-- Nathan Christiansen
> -----Original Message-----
> From: Nathan Christiansen
> Sent: Thursday, January 29, 2004 3:49 PM
> To: [EMAIL PROTECTED]
> Subject: MySQL outer join with substring_index() function
>
> I am having a terrible time trying to understand the outer join syntax
> for MySQL (Our sever version is: 3.23.56).
>
> I have three tables I want to join two of which by inner joins and the
> other by an outer join.
>
> Here are my example tables:
>
> table1:
> +------+------+
> | id | key |
> +------+------+
> | 4321 | 1111 |
> +------+------+
>
> table2:
> +------+------+-------------+
> | id | type | weird |
> +------+------+-------------+
> | 4321 | one | 1234-xxx-98 |
> | 4321 | two | NULL |
> +------+------+-------------+
>
> table3:
> +------+----------+-----+
> | prid | name | cid |
> +------+----------+-----+
> | 1234 | Success! | 98 |
> | 1234 | Failure! | 87 |
> +------+----------+-----+
>
>
> The following query gives me only one row with the contents of table2,
> row1 and t3, row1.name:
>
> select
> t2.*,
> t3.name
> from
> table1 t1,
> table2 t2,
> table3 t3
> where
> t1.key = 1111
> and
> t1.id = t2.id
> and
> t3.prid = substring_index(t2.weird, '-', 1)
> and
> t3.cid = substring_index(t2.weird, '-', -1);
>
>
> How do I change the query so that I get table2, row 2 as well with the
> name field blank (or NULL)?
>
> I thought I might use outer joins, but the syntax and online manual is
> just confusing me.
>
> Thanks.
>
> -- Nathan Christiansen
> Software Engineer
> Tahitian Noni International
> http://www.tahitiannoni.com
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
