Yes, but not quite as you imagined doing it.
SELECT tbl1.*, coalesce(broker, vendor) as company
from table1 tbl1
LEFT JOIN tblBroker b
on tbl1.broker_id = b.id
LEFT JOIN tblVendor v
ON tbl1.vendor_id = v.id;
The left joins indicates that the tables on right side of the join contain
optional information. For rows that do not match the ON conditions of each
JOIN, every column in the unmatched table will have a NULL value in it.
The COALESCE() function returns the first non-null value from the list of
values.
Give us more details about what you are trying to do and I am certain you
can get a more precise answer.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Klarenbach <[EMAIL PROTECTED]> wrote on 12/05/2005 02:43:58
PM:
> As an update to my earlier question, is it possible to have logic in
> select statements including the join? ie,
>
> select
> IF(CHAR_LENGTH(broker)>0,broker,vendor) as company
> from table 1
> IF(CHAR_LENGTH(broker)>0,INNER JOIN tblBroker,INNER JOIN tblVendor)
>
> Thanks.
>
>
> On 11/28/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> >
> > Scott Klarenbach <[EMAIL PROTECTED]> wrote on 11/28/2005
01:58:22
> > PM:
> >
> >
> > > I'd like to do the following in my view
> > >
> > > select
> > > fieldOne,
> > > fieldTwo,
> > > if(fieldThree.length>0) then fieldThree as Company
> > > else fieldFour as Company
> > >
> > > from table;
> > >
> > > I realize this syntax isn't correct and length doesn't exists, but
is
> > > this possible? I've seen it done in SQLServer, but can't remember
the
> > > syntax.
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> > >
> >
> > LENGTH does exist (as a function):
> > http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
> >
> > and here is how to use the IF() function:
> > http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
> >
> > I believe this is the equivalent statement you wanted:
> >
> > SELECT
> > fieldOne,
> > fieldTwo,
> > IF(CHAR_LENGTH(fieldThree)>0,fieldThree,fieldFour) as
> > Company
> > FROM table;
> >
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
