How to concatenate a constant to an int?

Hello:

This seems like it should be simple, but I am having trouble
figuring it out.

I have a table contact which has:
name String
contact_id int

Lets assume the contact table has this row:
name: Neil Aggarwal
contact_id: 1

I want to create a view that has this data
name: Neil Aggarwal
ref: C1

I did this:

create or replace view
view_AllData as
select
c.name
('C'+c.contact_id) as ref,
from contact c

but the ref does not have the leading 'C' in
the front of it.

Any ideas?

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

Re: How to concatenate a constant to an int?

create or replace view
view_AllData as
select
c.name,
concat('C',c.contact_id) as ref
from contact c


On Tue, Nov 24, 2009 at 6:52 PM, Neil Aggarwal wr=
ote:
> Hello:
>
> This seems like it should be simple, but I am having trouble
> figuring it out.
>
> I have a table contact which has:
> =A0 =A0 =A0 =A0name =A0 =A0 =A0 =A0 =A0 =A0String
> =A0 =A0 =A0 =A0contact_id =A0 =A0 =A0int
>
> Lets assume the contact table has this row:
> =A0 =A0 =A0 =A0name: Neil Aggarwal
> =A0 =A0 =A0 =A0contact_id: 1
>
> I want to create a view that has this data
> =A0 =A0 =A0 =A0name: Neil Aggarwal
> =A0 =A0 =A0 =A0ref: C1
>
> I did this:
>
> create or replace view
> view_AllData as
> select
> =A0 =A0 =A0 =A0c.name
> =A0 =A0 =A0 =A0('C'+c.contact_id) as ref,
> from contact c
>
> but the ref does not have the leading 'C' in
> the front of it.
>
> Any ideas?
>
> Thanks,
> =A0 =A0 =A0 =A0Neil
>
> --
> Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
> Host Joomla!, Wordpress, phpBB, or vBulletin for $25/mo
> Unmetered bandwidth =3D no overage charges, 7 day free trial
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
- michael dykman
- mdykman@gmail.com

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

Larry's First Law of Language Redesign: Everyone wants the colon.

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

RE: How to concatenate a constant to an int?

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

Re: How to concatenate a constant to an int?

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 =3D p.prospect_id
left join client l on p.prospect_id =3D 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 wr=
ote:
>> =A0 =A0 =A0 =A0 concat('C',c.contact_id) as ref
>
> That worked. =A0Thanks the the tip.
>
> Now, lets say I have three tables:
>
> contact
> =A0 =A0 =A0 =A0contact_id int
>
> prospect
> =A0 =A0 =A0 =A0prospect_id int
>
> client
> =A0 =A0 =A0 =A0client_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:
>
> =A0 =A0 =A0 =A0contact_id 1
>
> =A0 =A0 =A0 =A0contact_id 2
> =A0 =A0 =A0 =A0prospect_id 2
>
> =A0 =A0 =A0 =A0contact_id 3
> =A0 =A0 =A0 =A0prospect_id 3
> =A0 =A0 =A0 =A0client_id 3
>
> I want the ref numbers to be:
> =A0 =A0 =A0 =A0C1
> =A0 =A0 =A0 =A0P2
> =A0 =A0 =A0 =A0L3
>
> Is there a way to use a query to do that?
>
> Something like:
>
> create or replace view
> view_AllData as
> select
> =A0 =A0 =A0 =A0concat('C' or 'P' or 'L',c.contact_id) as reference_number=
,
> from contact c
> left join prospect p on c.contact_id =3D p.prospect_id
> left join client l on p.prospect_id =3D l.client_id
>
> Any ideas how to do this?
>
> Thanks
> =A0 =A0 =A0 =A0Neil
>
> --
> Neil Aggarwal, (281)846-8957, http://UnmeteredVPS.net
> Host Joomla!, Wordpress, phpBB, or vBulletin for $25/mo
> Unmetered bandwidth =3D no overage charges, 7 day free trial
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
- michael dykman
- mdykman@gmail.com

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

Larry's First Law of Language Redesign: Everyone wants the colon.

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