Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
Aged Yak Warrior

USA
550 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
30421 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
Aged Yak Warrior

USA
550 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  
 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.07 seconds. Powered By: Snitz Forums 2000