Selecting records in table only when ALL related

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Selecting records in table only when ALL related

Author

Topic

csorric
Starting Member

3 Posts

Posted - 2013-07-15 : 12:01:18

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

550 Posts

Posted - 2013-07-15 : 12:51:44

[code]

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

[/code]

csorric
Starting Member

3 Posts

Posted - 2013-07-15 : 13:37:56

I think you nailed it! i Have never used having like that before.....thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-15 : 13:39:44

[code]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;[/code]

N 56°04'39.26"
E 12°55'05.63"

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-07-15 : 19:03:18

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

csorric
Starting Member

3 Posts

Posted - 2013-07-17 : 12:06:40

Thanks ScottPletcher I did catch that.

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

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources