List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:November 25 2009 2:39am
Subject:Re: How to concatenate a constant to an int?
View as plain text  
untested, but you are looking for something like this (formatted for humans):

select
       concat(
          ifnull(
            if(l.client_id,'L',null),
            ifnull(
                if(p.prospect_id,'P',null),
                'C')),
       c.contact_id) as reference_number,
from contact c
left join prospect p on c.contact_id = p.prospect_id
left join client l on p.prospect_id = l.client_id

- md


ifnull( if(l.client_id,'L',null),
   ifnull(if(p.prospect_id,'P',null),'C'))


On Tue, Nov 24, 2009 at 8:43 PM, Neil Aggarwal <neil@stripped> wrote:
>>         concat('C',c.contact_id) as ref
>
> That worked.  Thanks the the tip.
>
> Now, lets say I have three tables:
>
> contact
>        contact_id int
>
> prospect
>        prospect_id int
>
> client
>        client_id int
>
>
> If a contact is a prospect, it will have a line in
> both the contact and prospect table, with the same
> id value.
>
> If a contact is a client, it will have a line in
> the contact, prospect, and client table, all with
> the same id value.
>
> For example:
>
>        contact_id 1
>
>        contact_id 2
>        prospect_id 2
>
>        contact_id 3
>        prospect_id 3
>        client_id 3
>
> I want the ref numbers to be:
>        C1
>        P2
>        L3
>
> Is there a way to use a query to do that?
>
> Something like:
>
> create or replace view
> view_AllData as
> select
>        concat('C' or 'P' or 'L',c.contact_id) as
> reference_number,
> from contact c
> left join prospect p on c.contact_id = p.prospect_id
> left join client l on p.prospect_id = l.client_id
>
> Any ideas how to do this?
>
> Thanks
>        Neil
>
> --
> Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
> Host Joomla!, Wordpress, phpBB, or vBulletin for $25/mo
> Unmetered bandwidth = no overage charges, 7 day free trial
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>



-- 
 - michael dykman
 - mdykman@stripped

"May you live every day of your life."
    Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.
Thread
How to concatenate a constant to an int?Neil Aggarwal25 Nov
  • Re: How to concatenate a constant to an int?Michael Dykman25 Nov
    • RE: How to concatenate a constant to an int?Neil Aggarwal25 Nov
      • Re: How to concatenate a constant to an int?Michael Dykman25 Nov