Query pulling too much information

[vsprogrammer]

Hi all!

I am trying to create a query for that will allow me to pull the following info:

Triglycerides, Cholesterol LEVEL, Hours Fasting, Total: HDL Cholesterol Ratio, HDL Cholesterol, and LDL Calculated.

This is what I have so far:

select distinct
rip.name 'Policy Name',
rci.itemname 'Item Name'
from
cv3relevantinfopolicy rip
join cv3relevantinfopolicydtl ripd on rip.guid = ripd.relevantinfopolicyguid
join cv3resultcatalogitem rci on rci.guid = ripd.relevantitemguid
where
rci.itemname
IN
(
'Triglycerides',
'Cholesterol LEVEL',
'Hours Fasting',
'Total: HDL Cholesterol Ratio',
'HDL Cholesterol',
'LDL Calculated'
)
order by
rip.name

This query however shows me policies that just have 'Triglycerides' or just has hours fasting, I need to see the policy that contains all the items (no more no less). I have tried to use "and" and "or" I have tried right joins and left joins but nothing seems to be working. I am using SQL 2005. I hope that this makes sense and that someone can point me in the right direction?

Thanks a bunch!
VSProgammer

 

[Qik3Coder]

rci.itemname
IN
(
'Triglycerides',
'Cholesterol LEVEL',
'Hours Fasting',
'Total: HDL Cholesterol Ratio',
'HDL Cholesterol',
'LDL Calculated'
)


should be:


rci.itemname LIKE '%Triglycerides%'
and rci.itemname LIKE '%Cholesterol LEVEL%'
...


By the way, it really scares the hell out of me when a SQL programmer with no idea what they are doing is working with medical records...



-Sometimes the answer to your question is the hack that works

 

[vsprogrammer]

Ah, take a read again! I already said that I tried that and for the record - I Do know what I am doing!

 

[ousoonerjoe]

worst comes to worst you can always do it the hard way:

select distinct
    rip.name 'Policy Name',
    rci.itemname 'Item Name'
from
    cv3relevantinfopolicy rip
    join cv3relevantinfopolicydtl ripd on rip.guid = ripd.relevantinfopolicyguid
    join cv3resultcatalogitem rci on rci.guid = ripd.relevantitemguid
where
    rci.itemname = 'Triglycerides'
order by
    rip.name

UNION ALL 

select distinct
    rip.name 'Policy Name',
    rci.itemname 'Item Name'
from
    cv3relevantinfopolicy rip
    join cv3relevantinfopolicydtl ripd on rip.guid = ripd.relevantinfopolicyguid
    join cv3resultcatalogitem rci on rci.guid = ripd.relevantitemguid
where
    rci.itemname = 'Cholesterol LEVEL'
order by
    rip.name

UNION ALL

select distinct
    rip.name 'Policy Name',
    rci.itemname 'Item Name'
from
    cv3relevantinfopolicy rip
    join cv3relevantinfopolicydtl ripd on rip.guid = ripd.relevantinfopolicyguid
    join cv3resultcatalogitem rci on rci.guid = ripd.relevantitemguid
where
    rci.itemname = 'Hours Fasting'
order by
    rip.name

etc...

nasty and over done, but should get you to where you need to be.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[SQLSister]

I'm taking it that tryglycerides, etc are stored in different rows in the table not in one big text column?
Something along this lines might get you what you want or at least give you some idea of waht to try (this is untested:

select rip.[name] 'Policy Name' from 
(select distinct 
    rip.[name] ,
    rci.itemname 
from 
    cv3relevantinfopolicy rip
    join cv3relevantinfopolicydtl ripd on rip.guid = ripd.relevantinfopolicyguid
    join cv3resultcatalogitem rci on rci.guid = ripd.relevantitemguid
where 
    rci.itemname 
IN 
    (
    'Triglycerides',
    'Cholesterol LEVEL',
    'Hours Fasting',
    'Total: HDL Cholesterol Ratio',
    'HDL Cholesterol',
    'LDL Calculated'
    )) a 
group by rip.[name] having count(*) = 6
order by 
    rip.[name]

"NOTHING is more important in a database than integrity." ESquared

 

[vsprogrammer]

Thanks ousoonerjoe and SQLSister! ousoonerjoe - yes that would work but would be too long sometimes as they may request a policy that has 50 items in it, but a good idea! SQLSister - I went with something along your lines, however, the # will change each time so I created a temp table and entered the info in there and then counted to see how many were there and stored that into a variable and then used the variable instead of "6".

Thanks so much for your help!! Greatly appreciated!
VSProgrammer