Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to write complex sql query?

Posted on 2011-02-28
10
Medium Priority
?
190 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
Independent Software Vendors: 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 1500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

609 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