Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query syntax

Posted on 2014-09-19
3
Medium Priority
?
120 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
[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
3 Comments
 
LVL 52

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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

704 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