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 <n...@jammconsulting.com> 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=mdyk...@gmail.com > > -- - michael dykman - mdyk...@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=arch...@jab.org