Solved

How to write complex sql query?

Posted on 2011-02-28
10
185 Views
Last Modified: 2012-05-11
Tablename - trainers
Fields: tid,trainername,flag
tid is unique primary key
trainerana,e is name of the trainer
flag contains either 0 or 1.
Write a query that returns all the trainers with thier tid and trainername.The output should replace tid with blank for those trainernames where flag is set to 1
0
Comment
Question by:searchsanjaysharma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 35002941
select case when flag = 1 then '' else cast( tid as varchar ) tid ,trainername,flag
from trainers
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 35002948
oops

select case when flag = 1 then '' else cast( tid as varchar ) end as tid ,trainername,flag
from trainers
0
 

Expert Comment

by:aldovargas
ID: 35003523
or try this:

select tid,trainername from trainers where flag <> 1
union all
select '' ,trainername from trainers where flag = 1
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Accepted Solution

by:
anushahanna earned 500 total points
ID: 35003620
you can avoid duplicates by

select distinct tid,trainername from trainers where flag = 0
union all
select distinct '' ,trainername from trainers where flag = 1

or

select tid,trainername from trainers where flag = 0
union
select '' ,trainername from trainers where flag = 1
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35004636
this appears to be "Homework"/"Coursework"  we are limited to what we can provide for such questions ...



please confiirm the background for your requirement....
0
 

Author Comment

by:searchsanjaysharma
ID: 35005839
This is not the homework for your kind informationl
secondly when i am paying i am suppose to get response and accept what suits my need.
thirdly i could easily wirte sp for it. but i wanted  the answer in 1 sql statement.
Anyways thanks for your Guess.
0
 

Author Closing Comment

by:searchsanjaysharma
ID: 35005877
THANKS
0
 

Author Comment

by:searchsanjaysharma
ID: 35016601
Thanks for your valuable comments.
0
 

Author Closing Comment

by:searchsanjaysharma
ID: 37767125
ok
0

Featured Post

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question