• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

SQL for Query - Multiple Tables - "ALL" Criteria

I am trying to write a query against 3 tables (attached shows relationship between the 3 tables).
There is a 1:Many relationship between table 1 (Test List) and table 2 (Test List Entry).
There is a 1:Many relationship between table 2 (Test List Entry) and table 3 (X_Analysis_Spcsrc)

Table 1 (TEST_LIST) field NAME
links to
Table 2 (TEST_LIST_ENTRY) field NAME

Table 2 (TEST_LIST_ENTRY) field ANALYSIS
links to
Table 3 (X_ANALYSIS_SPCSRC) field ANALYSIS

Table 3 also has the field SPECIMEN_SOURCE

The output I want is NAME from Table 1 (TEST_LIST) and SPECIMEN_SOURCE from Table 3 such that
ALL ANALYSIS in Table 2 have that SPECIMEN_SOURCE. (Critical is ALL)

For Example:
Table1.Name      Table2.Analysis                        Table3.Specimen_Source
HEPCHRONIC          HEPA            BLOOD
HEPCHRONIC          HEPA            PLASMA
HEPCHRONIC          HEPA            SERUM
HEPCHRONIC          HEPB            BLOOD
HEPCHRONIC          HEPB            SERUM
HEPCHRONIC          HEPC            BLOOD
HEPCHRONIC          HEPC            PLASMA
HIV          RAPID                                 BLOOD
HIV          RAPID                                 PLASMA
HIV          RAPID                                 URINE
HIV                          SLOW                                      BLOOD
HIV                          SLOW                                      PLASMA
HIV                          SLOW                                      SERUM

Output:
Table1.Name      Table3.Specimen_Source
HEPCHRONIC       BLOOD            (since all 3 analyses have BLOOD in Table 3)
HIV       BLOOD            (since all 2 analyses have BLOOD in Table 3)
HIV      PLASMA            (since all 2 analyses have PLASMA in Table 3)

Thanks much.
TestListAnalysisSpecSource.bmp
0
SusanLIMS
Asked:
SusanLIMS
  • 5
  • 3
  • 2
  • +2
1 Solution
 
brad2575Commented:
Select FieldListHere
From TEST_LIST TL
Left outer Join TEST_LIST_ENTRY TLE ON TL.NAME = TLE.NAME
LEFT OUTER JOIN X_ANALYSIS_SPCRC XAS ON TLE.ANALYSIS = XAS.ANALSIS
0
 
SusanLIMSAuthor Commented:
It's not that easy. The solution you gave gives me a record iin the output query f ANY analysis for that test list has that specimen source. I want a record in the output set ONLY if ALL analyses have that specimen source.  Please have a look at my example dataset and output data.

The query you gave me is a simple outer join. I believe my problem is much more complex.

I'm anxious for help. Thanks for trying.

0
 
Paul_Harris_FusionCommented:
Hi Susan,

A suggested approach for Access is as follows:
Create and save the query as you show in your picture and just select the NAME, ANALYSIS and SPECIMEN_SOURCE columns
Let us assume you have called this qSpecSource1

You could put a query around this one and use a group by with a HAVING clause
e.g.
select distinct NAME, MAX(SPECIMEN_SOURCE)
From qSpecSource1
GROUP BY NAME, ANALYSIS
HAVING MIN(SPECIMEN_SOURCE)=MAX(SPECIMEN_SOURCE)

You may want to check your first query and ensure that the inner/outer join behaviour is as you want it.  If using an outer join, you may want to transform NULLS into something that works predictably in your GROUP BY clause.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SusanLIMSAuthor Commented:
Just tried that - and it won't work either.  The query you suggested will only provide one qualifying specimen source per test_list.name.  There could be multiple qualifying specimen sources, as long as all associated analyses have that specimen source.
In the example I gave, see the Test List.Name = 'HIV', where the output dataset has both BLOOD and PLASMA specimen sources.
Thanks for the reply - I'm still anxious for some help on this one.
0
 
SharathData EngineerCommented:
Check this:
SELECT F.name,G.Specimen_Source
(SELECT A.name,C.Specimen_Source,Count(A.name) AS Count1
  FROM TEST_LIST A
  JOIN TEST_LIST_ENTRY B
    ON A.field = B.field
  JOIN X_ANALYSIS_SPCSRC C
    ON B.ANALYSIS = C.ANALYSIS
 GROUP BY A.Name, C.Specimen_Source) F
JOIN
(SELECT D.name,Count(D.name) AS Count2
  FROM TEST_LIST D
  JOIN TEST_LIST_ENTRY E
    ON D.field = E.field
 GROUP BY A.Name) G
 ON F.name = G.name
AND F.Count1 = G.Count2  
0
 
SusanLIMSAuthor Commented:
It seems like this is just counting up the total number of analyses on a test list and making sure it is the same as the analysis/specimen source combination.  There could be a situation where the counts are the same, but the values are different (i.e. one analysis has two specimen sources and another analysis on the same test list also has two specimen sources - but they are not the same two)
Do you agree that I am interpreting this correctly?  It still does not appear to be a robust solution.
If you thimk I'm wrong, pls let me know and I'll evaluate some more.  Otherwise, do you have any more ideas?
Thanks for the reply.
0
 
SharathData EngineerCommented:

I did not understand your concern fully. If you have any sample data set which explains your concern, i can work on it.  
Execute my query and check whether you got your output. From the sample data you have provided, i tried to design this solution.
Let me explain what i did in detail.
SELECT A.name,C.Specimen_Source,Count(A.name) AS Count1
 FROM TEST_LIST A
 JOIN TEST_LIST_ENTRY B
   ON A.field = B.field
  JOIN X_ANALYSIS_SPCSRC C
   ON B.ANALYSIS = C.ANALYSIS
GROUP BY A.Name, C.Specimen_Source
The result set of this query is:
name   Specimen_Source Count
HEPCHRONIC      BLOOD  3
HEPCHRONIC      PLASMA  2
HEPCHRONIC      SERUM  2
HIV  BLOOD  2
HIV  PLASMA  2
HIV  URINE  1
HIV  SERUM  1
From this SELECT query, I am trying to get the counts for name for the combination of name and Spicemen_Source.  This count is nothing but the ALL Analysis for the same name and Spicemen_Source.
SELECT D.name,Count(D.name) AS Count2
 FROM TEST_LIST D
 JOIN TEST_LIST_ENTRY E
   ON D.field = E.field
 GROUP BY A.Name
In this query, I am trying to get all Analysis for a name.
The result set of this query is:
name   Count
HEPCHRONIC  3
HIV  2
Matching the COUNTS between both the queries will give you the Specimen_Source for ALL ANALYSIS.
 
 
0
 
Paul_Harris_FusionCommented:
Assuming your diagrammed query results are in a table or query called QUERY_RESULTS

create a query called qANALYSIS_COUNT with the following SQL:
Select NAME, COUNT(0) as ANALYSIS_COUNT  FROM
( SELECT DISTINCT NAME, ANALYSIS
FROM QUERY_RESULTS
) ILV
GROUP BY NAME

Then create another query qSOURCE_COUNT from the following sql
SELECT DISTINCT NAME, SPECIMEN_SOURCE, Count(0) As SOURCE_COUNT
FROM QUERY_RESULTS
GROUP BY NAME, SPECIMEN_SOURCE


Finally create qRESULT to cross reference these two queries where the counts are the same
SELECT qSOURCE_COUNT.NAME, qSOURCE_COUNT.SPECIMEN_SOURCE, qSOURCE_COUNT.SOURCE_COUNT
FROM qSOURCE_COUNT INNER JOIN qANALYSIS_COUNT ON qSOURCE_COUNT.NAME = qANALYSIS_COUNT.NAME
WHERE (((qSOURCE_COUNT.SOURCE_COUNT)=[qANALYSIS_COUNT].[ANALYSIS_COUNT]));

I have attached an mdb file with these queries in

It seems to work!   Hopefully, I've understood the problem correctly


SuggestedSolution.mdb
0
 
Olaf DoschkeSoftware DeveloperCommented:
Susan, Sharath solution seems to be valid for what you are outlining.

But I wonder if there shouldn't be a parent table of patients or tests, because it seems to me that grouping records by test_list.name is not sufficient, it may group records not belonging together, if you do many HIV tests for diffferent patients and those HIV tests (TEST) consists of several analysis (TEST_LIST), of which each have several analysis (TEST_LIST_ENTRY) with several specimen (X_ANALYSIS_SPCSRC), you'd merge the test results of different patients specimen, if they will be under the same roof of being HIV tests. That would be very bad, wouldn't it? You'd either find out no patient will be positive by all analyisi of all specimens, if you only have one patient HIV negative.

I'd recommend doing it in small steps you can understand and with intermediate results you can understand, as it's more important here you get the correct result, than getting it with a single SQL.

You should get someone knowing the whole database strucuture inside out helping you to get the result.

Bye, Olaf.
0
 
Olaf DoschkeSoftware DeveloperCommented:
In your example you have HEPCHRONIC,BLOOD in the result (with my veto, that HEPCHRONIC is not specific enough for the grouping), because you have blood specimen for all the analysis done HEPA,B and C.

Plasma is not in the result because you have no HEPAB Plasma Analysis. Even if you had three plasma specimen but two of them were tested with a HEPAC analysis, that would make three specimens for three analysis but still no coupling of HEPAB analysis of a plasma specimen, so a pure count(*) match is not sufficient. In that respect you still have no correct answer.

Bye, Olaf.
0
 
SusanLIMSAuthor Commented:
Sharath - you were correct in your approach.  I was getting syntax errors on the query, so I played around with the joins - I changed the joins to the where clause since I'm more familiar with that syntax and I couldn't figure out where the errors were coming from in what you gave me.  Also G.specimen_source on first line was really from F, and group by in second subquery was on D not A.  No complaints about any of that - I got it all to work and the output is exactly what I wanted.
For completeness, here is the query:
SELECT F.NAME, F.SPECIMEN_SOURCE
FROM
(SELECT A.name, C.Specimen_Source, Count(A.name) AS Count1
FROM TEST_LIST A, TEST_LIST_ENTRY B, X_ANALYSIS_SPCSRC C
WHERE A.NAME = B.NAME AND B.ANALYSIS = C.ANALYSIS
GROUP BY A.Name, C.Specimen_Source
) AS F,
(SELECT D.name,Count(D.name) AS Count2
 FROM TEST_LIST D, TEST_LIST_ENTRY E
WHERE D.NAME = E.NAME
 GROUP BY D.Name
) AS G
WHERE F.NAME = G.NAME
AND F.COUNT1 = G.COUNT2;
I also realized that I didn't need the TEST_LIST table for the output I'm after - since TEST_LIST.NAME = TEST_LIST_ENTRY.NAME.  So what I really ended up with is:
SELECT F.NAME AS TEST_LIST, F.SPECIMEN_SOURCE
FROM
(SELECT TEST_LIST_ENTRY.NAME, X_ANALYSIS_SPCSRC.Specimen_Source, Count(TEST_LIST_ENTRY.NAME) AS Count1
FROM TEST_LIST_ENTRY, X_ANALYSIS_SPCSRC
WHERE TEST_LIST_ENTRY.ANALYSIS =X_ANALYSIS_SPCSRC.ANALYSIS
GROUP BY TEST_LIST_ENTRY.NAME, X_ANALYSIS_SPCSRC.SPECIMEN_SOURCE
) AS F,
(SELECT TEST_LIST_ENTRY.NAME,Count(TEST_LIST_ENTRY.NAME) AS Count2
 FROM TEST_LIST_ENTRY
 GROUP BY TEST_LIST_ENTRY.NAME
) AS G
WHERE F.NAME = G.NAME
AND F.COUNT1 = G.COUNT2;

I did this testing in MS Access.  I need to now test it in SQL Server and Oracle since I have this deployed in mulitple environments.
Thanks so much for your help,
0
 
SusanLIMSAuthor Commented:
Paul - I did not evaluate what you sent me since I managed to get Sharath's query to work.  Thanks for taking the time to respond.
Olaf - I actually am very familiar with the entire database structure.  I didn't explain what was really going on with the problem - but in case you are interested:  A Test_List is a set of tests to be assigned to a patient (not testing that has already been done).  When a specimen is received, it is of a specific specimen_source, i.e. BLOOD.  I know for each analysis what specimen sources are valid for testing.  If I have a BLOOD sample, I need to know which test_lists can be assigned (i.e. all the analyses are valid for testing on this specimen source).  
Thanks to all who took the time to respond.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Hi Susan,

okay, now I understand this is for planning doable analysis. Thanks for clearing that up.

Bye, Olaf.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now