Solved

How to write complex sql query?

Posted on 2011-02-28
10
183 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
Technology Partners: 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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Restore Script - Syntax Error 8 106
Sql Server group by 10 49
Find unused columns in a table 12 68
SQL parsing XML works but want to do it another way 4 18
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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 …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

762 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