Solved

How to write complex sql query?

Posted on 2011-02-28
10
182 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql calculate reminders 11 73
MS SQL page split per second is high 19 95
T-SQL to Update Table Dynamically 2 47
SQL Syntax:  How to Find Commonality Among Similar Results 2 48
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 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