List:General Discussion« Previous MessageNext Message »
From:SGreen Date:December 5 2005 8:10pm
Subject:Re: How to use Logic in View Statment?
View as plain text  
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 <doyouunderstand@stripped> 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, SGreen@stripped <SGreen@stripped> wrote:
> >
> >
> > Scott Klarenbach <doyouunderstand@stripped> 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/mysql?unsub=1
> > >
> >
> > 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/mysql?unsub=1
> 

Thread
How to use Logic in View Statment?Scott Klarenbach28 Nov
  • Re: How to use Logic in View Statment?SGreen28 Nov
    • Re: How to use Logic in View Statment?Cory @ SkyVantage28 Nov
      • Re: How to use Logic in View Statment?SGreen28 Nov
    • Re: How to use Logic in View Statment?Martijn Tonies28 Nov
    • Re: How to use Logic in View Statment?Scott Klarenbach5 Dec
      • Re: How to use Logic in View Statment?SGreen5 Dec
  • Re: How to use Logic in View Statment?Peter Brawley28 Nov
RE: How to use Logic in View Statment?Gelu Gogancea28 Nov