SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Selecting records in table only when ALL related
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

csorric
Starting Member

USA
3 Posts

Posted - 07/15/2013 :  12:01:18  Show Profile  Reply with Quote
Hello and thank you for your time. I feel like this should be simple but I can’t seem to work it out….maybe I have been working on it so long that I need another perspective.

I have a Lab_result_obr_p table that contains labs that have been resulted: The PK is LabNum which is the lab Number and the FK is OrderNum which is the order number.

So in this table one order# can have multiple lab#s
Table: Lab_result_obr_p:

LabNum OrderNum
A29E02FE D2C6DDA8
4A563D24 D2C6DDA8
0F53BC60 DC01E4EB
GS43A689 DC01E4EB
F943C7034 DF2654G7

The other table is Lab_rsults_obx and it contains the lab components and individual component results. In this table, each lab# from above will have multiple lab comments and results

Table: Lab_rsults_obx

LabNum Lab_C Flag Value
A29E02FE WBC N 3.5
A29E02FE RBC N 260
4A563D24 LMP: N 50
4A563D24 RH TYPE N DNR
0F53BC60 BACTERIA N TNP
GS43A689 MCV N 30
GS43A689 MCH N 40
F943C7034 RH TYPE Y Negative


I need to select all LabNum's from Lab_result_obr_p where all components of all labs has a Abnormal_Flag of N and does not have a value of TNP. So if an Order has two labs, I need all the components for both labs to have an N for Abnormal_Flag and to not have a value of TNP for the order number to be selected

So for the data above my result would look like:

ngn_order_num
D2C6DDA8

I am not posting exactly what I have tired becuase I think i need a fresh prerspective. But I have tried all kinds of subqueries and joins using exists and in
I am open to using temp tables if needed......thanks

ScottPletcher
Constraint Violating Yak Guru

USA
411 Posts

Posted - 07/15/2013 :  12:51:44  Show Profile  Reply with Quote


SELECT
    lrop.OrderNum
FROM dbo.Lab_result_obr_p lrop
INNER JOIN dbo.Lab_results_obx lrox ON
    lrox.LabNum = lrop.OrderNum
GROUP BY
    lrop.OrderNum
HAVING
    SUM(CASE WHEN lrox.Flag = 'N' THEN 1 ELSE 0 END) = COUNT(lrox.Flag) AND
    MAX(CASE WHEN lrox.Value = 'TNP' THEN 1 ELSE 0 END) = 0
ORDER BY
    lrop.OrderNum

Go to Top of Page

csorric
Starting Member

USA
3 Posts

Posted - 07/15/2013 :  13:37:56  Show Profile  Reply with Quote
I think you nailed it! i Have never used having like that before.....thank you
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/15/2013 :  13:39:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
DECLARE	@Orders TABLE
	(
		LabNum VARCHAR(9) NOT NULL,
		OrderNum CHAR(8) NOT NULL
	);

INSERT	@Orders
	(
		LabNum,
		OrderNum
	)
VALUES	('A29E02FE', 'D2C6DDA8'),
	('4A563D24', 'D2C6DDA8'),
	('0F53BC60', 'DC01E4EB'),
	('GS43A689', 'DC01E4EB'),
	('F943C7034', 'DF2654G7');

DECLARE	@Results TABLE
	(
		LabNum VARCHAR(9) NOT NULL,
		Lab_C VARCHAR(8) NOT NULL,
		Flag CHAR(1) NOT NULL,
		Value VARCHAR(8) NOT NULL
	);

INSERT	@Results
	(
		LabNum,
		Lab_C,
		Flag,
		Value
	)
VALUES	('A29E02FE', 'WBC', 'N', '3.5'),
	('A29E02FE', 'RBC', 'N', '260'),
	('4A563D24', 'LMP:', 'N', '50'),
	('4A563D24', 'RH TYPE', 'N', 'DNR'),
	('0F53BC60', 'BACTERIA', 'N', 'TNP'),
	('GS43A689', 'MCV', 'N', '30'),
	('GS43A689', 'MCH', 'N', '40'),
	('F943C7034', 'RH TYPE', 'Y', 'Negative');

-- SwePeso
SELECT		p.OrderNum
FROM		@Orders AS p
INNER JOIN	@Results AS x ON x.LabNum = p.LabNum
GROUP BY	p.OrderNum
HAVING		MIN(CASE WHEN x.Flag = 'N' AND x.Value <> 'TNP' THEN 1 ELSE 0 END) = 1;



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
411 Posts

Posted - 07/15/2013 :  19:03:18  Show Profile  Reply with Quote
I suspect you already noticed this, but sorry, I had a typo in the INNER JOIN on my original query:


INNER JOIN dbo.Lab_results_obx lrox ON
lrox.LabNum = lrop.LabNum
Go to Top of Page

csorric
Starting Member

USA
3 Posts

Posted - 07/17/2013 :  12:06:40  Show Profile  Reply with Quote
Thanks ScottPletcher I did catch that.

SwePeso, yours works perfect as well and you gave me a better idea on how to post

thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000