Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

adding other users to an existing query: looking for the most simplified answer

select
   case profile_id when 'bob' then 'bob evans' else substring(profile_id,1,3) end,
   case sender when 'bob' then 'bob evans' else substring(sender,1,3) end,
   message_id,
   case this_user when 'bob' then 'bob evans' else substring(this_user,1,3) end


select column1 from table1

if column1='bob', write 'bob evans'

select case column1 when 'bob' then 'bob evans' else column1 end as MyColumn
from table1


How to add this new case statement to this existing query
select substring(profile_id,1,3),substring(sender,1,3),message_id, substring(this_user,1,3)

where 'bob' can be profile_id, sender, this_user

NOTE: I want 'bob evans' else three letters of column name
Do not do substring if 'bob evans'



ADDING TO THIS QUESTION:

THERE ARE 5 OTHER USERS

'susan': 'susan jones'
'abe' : 'abe thomas'
'mary': 'mary davis'
'dave'; 'dave parker'
'joe': 'joe advo'
0
rgb192
Asked:
rgb192
  • 2
2 Solutions
 
mankowitzCommented:
First make a table for your common people:

create table people (
  abbrev varchar(5),
  fullname varchar(14)
  );

insert people (abbrev, fullname) VALUES
('susan', 'susan jones'),
('abe', 'abe thomas'),
('mary', 'mary davis'),
('dave', 'dave parker'),
('joe', 'joe advo')

Then, the query might look like this:

select
   coalesce((select fullname from people where abbrev=profile_id),
            substring(profile_id,1,3)),
   coalesce((select fullname from people where abbrev=sender),
            substring(sender_id,1,3)),
   message_id,
   coalesce((select fullname from people where abbrev=this_user),
            substring(this_user,1,3))
0
 
mankowitzCommented:
0
 
Ray PaseurCommented:
In database design there is the ACID concept, and the meaning can of "atomic" can be extended to the meanings for columns.  Each column should mean one thing and one thing only.  It's perfectly sensible to have the data base tables organized so that variants of the data are replicated.  I often do this with things that get misspelled a lot, like city names.  I keep both soundex() and metaphone() variants in the same row.  This lets me search for the city name as received from client input, but also search for similar-sounding rows.  If I miss on the name but match on the soundex or metaphone it lets me reply "Did you mean...?"

You might consider a design that had the three-letter search key abbreviation and the data you want to retrieve in separate columns:
 sKey   fName    fullName        responseData
'sus'  'susan'  'susan jones'   'sus'
'abe'  'abe'    'abe thomas'    'abe'
'mar'  'mary'   'mary davis'    'mar'
'bob'  'bob'    'robert evans'  'bob evans'

Open in new window

This will make your queries much easier to write, and if the data base ever grows to a large size it will scale up very smoothly.  The query would now say:
SELECT responseData FROM myTable WHERE sKey = $sKey LIMIT 1
0
 
rgb192Author Commented:
Thanks for the sql code and the database acid tutorial
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now