Solved

Query syntax

Posted on 2014-09-19
3
110 Views
Last Modified: 2014-09-19
Hello,

I have a table that contains rows of data for clinics.  Basically each row has the clinicid and then a load of data regarding performance.

There is also a row where the clinicid is -1 (not a valid clinic) and this holds the summarised data for the whole table.

If I want to select a single clinics data, and the summarized data what would the general syntax be?

Give clinicid of 10 I am looking for:
10, clinic specific data, -1, summerised data

Open in new window


Is the query something like the example below, or is there a better syntax

Given that the column names are clinicid, data1, data2, data3 etc.

Select 
@clinicid as ClinicId
Case clinicid = @clinicid then data1 else null end as clinicData1,
Case clinicid = @clinicid then data2 else null end as clinicData2,
Case clinicid = @clinicid then data3 else null end as clinicData3,
-1 as Summary,
Case clinicid = -1 then data1 else null end as SummaryData1,
Case clinicid = -1 then data2 else null end as SummaryData1,
Case clinicid = -1 then data3  else null end as SummaryData1
From MyTable

Open in new window

0
Comment
Question by:soozh
3 Comments
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40332682
Can you post the table structure and a sample of data? And also an example of the expected output?
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 40332743
I am not sure I understand what you are trying to do.  
But perhaps you can use a subquery as follows:

Select ClinicID, Data, (Select ClinicID from MyTable where ClinicID=-1)
from MyTable
Where ClinicID <>-1

The subquery can pull up your summerized data along side your other records.
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 40333788
If you don't mind having 2 rows:
SELECT 	*
FROM	MyTable
WHERE	clinicid IN (@clinicid,-1)

Open in new window

If both records are needed on the same row:
SELECT	*
FROM	(	SELECT 	*
		FROM	MyTable
		WHERE	clinicid = @clinicid
	) a
CROSS
JOIN	(	SELECT 	*
		FROM	MyTable
		WHERE	clinicid = -1
	) b

Open in new window

0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

943 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

8 Experts available now in Live!

Get 1:1 Help Now