Solved

How to write complex sql query?

Posted on 2011-02-28
10
180 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
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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Stored Procedure error 45 47
How to place a condition in a filter criteria in t-sql? 12 58
CROSS APPLY 4 44
Unable to save view in SSMS 21 57
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now