Solved

SQL Query Using UNION/EXISTS/INTERSECT To Gather Common Data on Same Tables

Posted on 2011-03-17
23
379 Views
Last Modified: 2012-06-21
Hi,
I've attempted a few possible ways to write this query, but I'm either missing something simple, or it's more complicated than my current SQL skills can handle. In a nutshell the user has an expanded search page where they can select various criteria. I dynamically build the where clause portion of the query to produce the results based on their selections. There are 2 tables (one to many relationship). If they only make one selection the query looks like snippet 1, 2 selections looks like snippet 2, etc. Using the "AND" operator in snippet 2 produces zero results because as you'll see the same column is being referenced for the passed data. Using the "OR" brings back everything, but I want what is common among all of the user's selection.

"UNION" doesn't work (snippet 3) because it brings back everything for each query and I would like what is common among each. I tried using "INTERSECT", but as I understand it it is only functional in the Analysis Services feature of SQL Server (correct me if I'm wrong on this). At least setting it up provoked a syntax error in SQL Server Management Studio. I also tried employing "EXISTS", but I seem to get the same results as with "UNION" when working with multiple selection choices (snippet 4).

Anyway, could someone point me in the right direction? The key element here is that what would traditonal an easy situation is complicated by the fact that I'm working with fields that are not set up as columns but are instead dynamic rows of data (Book2.xls, which is produced by the UNION query. I highlighted the records that I'd like to come back since they contain both selection choices by the user). "Study_Template_Item" would be considered the column if it were a "hard coded" field.

Cheers,
Ty

PS: I'm using SQL Server 2008
snippet 1

SELECT     
PreclinicalStudyMaster.Preclinical_Study_Master_ID, PreclinicalStudyMaster.Preclinical_Study_Master_Code_ID,
PreclinicalStudyItems.Study_Template_Item, PreclinicalStudyItems.Study_Template_Item_Value
FROM         PreclinicalStudyItems INNER JOIN
                      PreclinicalStudyMaster ON PreclinicalStudyItems.Preclinical_Study_Master_ID = PreclinicalStudyMaster.Preclinical_Study_Master_ID
WHERE     
(PreclinicalStudyItems.Study_Template_Item = 'Device Comments') AND 
(PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Device%') 

UNION

SELECT     
PreclinicalStudyMaster.Preclinical_Study_Master_ID, PreclinicalStudyMaster.Preclinical_Study_Master_Code_ID,
PreclinicalStudyItems.Study_Template_Item, PreclinicalStudyItems.Study_Template_Item_Value
FROM         PreclinicalStudyItems INNER JOIN
                      PreclinicalStudyMaster ON PreclinicalStudyItems.Preclinical_Study_Master_ID = PreclinicalStudyMaster.Preclinical_Study_Master_ID
WHERE
(PreclinicalStudyItems.Study_Template_Item = 'Director' AND 
PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Dawn%')

ORDER BY PreclinicalStudyMaster.Preclinical_Study_Master_ID

Open in new window

snippet 2

SELECT     
 PreclinicalStudyMaster.Preclinical_Study_Master_ID, 
 PreclinicalStudyMaster.Preclinical_Study_Master_Code_ID,
 PreclinicalStudyItems.Study_Template_Item, 
 PreclinicalStudyItems.Study_Template_Item_Value
FROM
 PreclinicalStudyItems INNER JOIN
 PreclinicalStudyMaster ON PreclinicalStudyItems.Preclinical_Study_Master_ID = PreclinicalStudyMaster.Preclinical_Study_Master_ID
WHERE     
(PreclinicalStudyItems.Study_Template_Item = 'Device  Comments' AND PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Device%')
AND
(PreclinicalStudyItems.Study_Template_Item = Director' AND PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Dawn%')

Open in new window

snippet 4

SELECT     
 PreclinicalStudyMaster.Preclinical_Study_Master_ID, 
 PreclinicalStudyMaster.Preclinical_Study_Master_Code_ID,
 PreclinicalStudyItems.Study_Template_Item, 
 PreclinicalStudyItems.Study_Template_Item_Value
FROM
 PreclinicalStudyItems INNER JOIN
 PreclinicalStudyMaster ON PreclinicalStudyItems.Preclinical_Study_Master_ID = PreclinicalStudyMaster.Preclinical_Study_Master_ID
WHERE     
 (PreclinicalStudyItems.Study_Template_Item = 'Device  Comments' AND PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Device%')

Open in new window

snippet 3

SELECT     
a.Preclinical_Study_Master_ID, a.Preclinical_Study_Master_Code_ID
FROM         PreclinicalStudyItems INNER JOIN
                      PreclinicalStudyMaster AS a ON 
                      PreclinicalStudyItems.Preclinical_Study_Master_ID = a.Preclinical_Study_Master_ID 
WHERE EXISTS
(
	SELECT * FROM PreclinicalStudyItems AS b
	WHERE a.Preclinical_Study_Master_ID = b.Preclinical_Study_Master_ID AND
	(PreclinicalStudyItems.Study_Template_Item = 'Device Comments' AND 
	PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Device%')
)

AND
	EXISTS
	(
	SELECT * FROM PreclinicalStudyItems AS c
	WHERE a.Preclinical_Study_Master_ID = c.Preclinical_Study_Master_ID AND
	(PreclinicalStudyItems.Study_Template_Item = 'Director' AND 
	PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Dawn%') 
	)

Open in new window

Book2.xls
0
Comment
Question by:MedtronicVascSR
  • 10
  • 9
  • 2
  • +1
23 Comments
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
try this:


WHERE (PreclinicalStudyItems.Study_Template_Item IN ( 'Device  Comments', 'Director' )
   AND ((PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Device%') OR (PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Dawn%'))
0
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
Actually, I do see a problem with my suggestion in that rows will be returned for item='Director' and value like '%Device%', which would not be correct, but would only be a problem if such rows exist.  But it is a flaw in principle.
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
Ok, this:
WHERE    
(PreclinicalStudyItems.Study_Template_Item = 'Device  Comments' AND PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Device%')
AND
(PreclinicalStudyItems.Study_Template_Item = Director' AND PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Dawn%')

will return all records where Study_Template_Item is both 'Device  Comments' and 'Director' (I'm assuming you're missing a '). Seeing as the same field can't be both values, it will always return 0 records.

(PreclinicalStudyItems.Study_Template_Item = 'Device  Comments' AND PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Device%')
OR
(PreclinicalStudyItems.Study_Template_Item = Director' AND PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Dawn%')

This would return the records where Study_Template_Item is either 'Device  Comments' or 'Director' (along with the other conditions, but the logic is the same).
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
To clarify, what you have in snippet 2 would always be empty. But if you replace it with the OR I suggested, it should work. Unless you want some other result returned, in which case you should clarify what you expect to get.
0
 

Author Comment

by:MedtronicVascSR
Comment Utility
Hi,
Yes, my copy paste to this post somehow omitted the single quote around "Director"

Yes, "OR" does bring back everything. However, the result set I would like back are the records that  meet both the user's selection requirments, 'Device  Comments' that have the phrase "Device" in it as well as 'Director' that has the phrase "Dawn" in it. So instead of getting back the 42 or so records that meets each of the requirements individually there should be 3 records that meet both requirements.

Cheers,
Ty
0
 

Expert Comment

by:NigelBulley
Comment Utility
The answer is to build your SQL statement dynamically.

you can build it dynamically as a string like so :-

declare @strSQL nvarchar(4000)
declare @intCriteria int

Set @strSQL="SELECT    
PreclinicalStudyMaster.Preclinical_Study_Master_ID, PreclinicalStudyMaster.Preclinical_Study_Master_Code_ID,
PreclinicalStudyItems.Study_Template_Item, PreclinicalStudyItems.Study_Template_Item_Value
FROM         PreclinicalStudyItems INNER JOIN
                      PreclinicalStudyMaster ON PreclinicalStudyItems.Preclinical_Study_Master_ID = PreclinicalStudyMaster.Preclinical_Study_Master_ID
WHERE  "


@strSQL=@strSQL + " (PreclinicalStudyItems.Study_Template_Item = 'Device Comments') AND
(PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Device%') "

Then for each filter criteria you add one of these babys :-

If @intCriteria>1
BEGIN
      @strSQL = @strSQL + " OR  "
"(PreclinicalStudyItems.Study_Template_Item = 'Director' AND
PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Dawn%')"
END

If @intCriteria>2
BEGIN
      @strSQL = @strSQL + " OR  "
"(PreclinicalStudyItems.Study_Template_Item = 'Somthing else' AND
PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Ano Value%')"
END

And so on and so forth

Then run the SQL with :-
Exec ( @strSQL)

One thing though you will need to make sure a hacker does not exploit this system as any other SQL can be added to the end like so
    @strSQL = @strSQL + "; DELETE FROM PreclinicalStudyMaster "

Bye for now and "Free Gary Mkenna Now!!


0
 

Accepted Solution

by:
MedtronicVascSR earned 0 total points
Comment Utility
Hi,
NigelBulley, I am already building the query dynamically. I think the use of "OR" brings back all the related records versus just the one that meets them both.

Cheers,
Ty
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
Ok, what you need is to build dinamically but slightly different:

WHERE    
(PreclinicalStudyItems.Study_Template_Item LIKE '%Device  Comments%' AND PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Device%')
AND
(PreclinicalStudyItems.Study_Template_Item = LIKE '%Director%' AND PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Dawn%')

From what you said, I'm assuming this is what you want. The field will have something like: 'blah blah text Device Comments blah blah more text Director blah text'. If so, this will catch it. Instead of trying for a perfect match with =, you have to use LIKE instead.
0
 

Author Comment

by:MedtronicVascSR
Comment Utility
Cluskitt, I thought you might have had something there, but unfortunately, nothing comes back.

Cheers,
Ty
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
Sorry, my bad... not:
...)
AND
(...

but

...)
OR
(...
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
er... nevermind... not OR. I meant:

WHERE    
(PreclinicalStudyItems.Study_Template_Item LIKE '%Device  Comments%' AND PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Device%')
AND
(PreclinicalStudyItems.Study_Template_Item LIKE '%Director%' AND PreclinicalStudyItems.Study_Template_Item_Value LIKE '%Dawn%')


What this will do is: if Study_Template_Item has both 'Device  Comments' and 'Director' AND Study_Template_Item_Value has both 'Device' and 'Dawn', then it will return a record. Note that, if Study_Template_Item has both values, but Study_Template_Item_Value has only onw, then it won't return anything.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
Also, you should debug that the info is there. Try first:

SELECT *
FROM
 PreclinicalStudyItems INNER JOIN
 PreclinicalStudyMaster ON PreclinicalStudyItems.Preclinical_Study_Master_ID = PreclinicalStudyMaster.Preclinical_Study_Master_ID


And check for some cases where it should return after you add the WHERE clause. Then you can try debugging why it doesn't return any records.
0
 

Expert Comment

by:NigelBulley
Comment Utility
The expert is correct as AND is what you need.
0
 

Author Comment

by:MedtronicVascSR
Comment Utility
Cluskitt, the last query you posted looks the same as what you posted 3 postings back.

Yes, I have verified there are records that match the individual queries. My file upload, Book2.xls, shows the output from a UNION query, which would represent 2 individual searches.

NigelBulley, ok, but using "AND" produces no results, and I have verified that I would get back 3 records if we have structured it correctly.

The big challenge here, and we may be going about this the wrong way, is that we're trying to pass mulitple values to the same column, Study_Template_Item and telling it to be more than one thing at a time. I'm not sure how to get around this obstacle. I almost need a subset of the UNION query, or what INTERSECT purports to do.

Cheers,
Ty
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
I'm not sure what the original data is like and how it returned those values. Can you post some examples, please? The problem is that we're not really understanding how the data is in the database, so we can select the correct query. If I'm not mistaken, what you want can be done with multiple exists. But you're saying you want what's common, so I'm not sure what's expected.
0
 

Author Comment

by:MedtronicVascSR
Comment Utility
Hi,
I've attached what the data looks like raw from the 2 tables when they are joined in their one to many relationship. Study_Template_Item is the name of the field and Study_Template_Item_Value is what the user has entered for that field name. This is all done via another interface and not the search interface I'm referencing for my question.

I've highlighted what one "Study" record looks like in the database. Remember, columns names are now rows of data.

Hope this adds clarification.

Cheers,
Ty
Book3.xls
0
 

Author Comment

by:MedtronicVascSR
Comment Utility
Hi,
I think I've worked out one solution. Based on the number of selections a user makes on the search criteria interface I can test for the number of matches it makes like the following snippet. The 2 at the end is dynamic based on the number of filters that are dynamically used. Once I get this then I can run my original query to bring back those studies that match the study ID.

Cheers,
Ty


SELECT COUNT(Preclinical_Study_Master_ID), Preclinical_Study_Master_Code_ID
FROM
( 
SELECT     
A.Preclinical_Study_Master_ID, A.Preclinical_Study_Master_Code_ID,
AA.Study_Template_Item, AA.Study_Template_Item_Value
FROM PreclinicalStudyItems AA INNER JOIN
     PreclinicalStudyMaster A ON AA.Preclinical_Study_Master_ID = A.Preclinical_Study_Master_ID
WHERE     
	(AA.Study_Template_Item = 'Device Comments') AND 
	(AA.Study_Template_Item_Value LIKE '%Device%') 

UNION ALL

SELECT     
B.Preclinical_Study_Master_ID, B.Preclinical_Study_Master_Code_ID,
BB.Study_Template_Item, BB.Study_Template_Item_Value
FROM         PreclinicalStudyItems BB INNER JOIN
                      PreclinicalStudyMaster B ON BB.Preclinical_Study_Master_ID = B.Preclinical_Study_Master_ID
WHERE
(BB.Study_Template_Item = 'Director' AND 
 BB.Study_Template_Item_Value LIKE '%Dawn%')

) 
TMP
GROUP BY
Preclinical_Study_Master_Code_ID,
Preclinical_Study_Master_Code_ID
HAVING COUNT(Preclinical_Study_Master_ID) = 2

Open in new window

0
 

Assisted Solution

by:MedtronicVascSR
MedtronicVascSR earned 0 total points
Comment Utility
Attached is the final, cleaner version of code to get what I need in this situation. In essence I dynamically build the multiple "UNION" portion of the query based on the number of search criteria the user has selected. The "UNION" will bring back all that we want and more like we've discussed thoughout this post. But, since we've embedded this part of the query within a larger query we can look for the count of how many times the unique ID for the study record comes up and compare that to the number of filters applied. In this case we've used 3 and so we ask it to bring back only those unique ID's that hit on all 3 UNION queries. With that data I can now embed it into a larger "WHERE IN" query that pulls back the data I need to display to the user.

Thanks for all the imput today!

Cheers,
Ty
SELECT 
Preclinical_Study_Master_ID, COUNT(Preclinical_Study_Master_Code_ID) AS CT
FROM

( 
SELECT     
A.Preclinical_Study_Master_ID, 
A.Preclinical_Study_Master_Code_ID
FROM         PreclinicalStudyItems AA INNER JOIN
                      PreclinicalStudyMaster A ON AA.Preclinical_Study_Master_ID = A.Preclinical_Study_Master_ID
WHERE     
	(AA.Study_Template_Item = 'Device Comments') AND 
	(AA.Study_Template_Item_Value LIKE '%Device%') 

UNION ALL

SELECT     
B.Preclinical_Study_Master_ID, 
B.Preclinical_Study_Master_Code_ID
FROM         PreclinicalStudyItems BB INNER JOIN
                      PreclinicalStudyMaster B ON BB.Preclinical_Study_Master_ID = B.Preclinical_Study_Master_ID
WHERE
(BB.Study_Template_Item = 'Director' AND 
 BB.Study_Template_Item_Value LIKE '%Dawn%')

UNION ALL

SELECT     
C.Preclinical_Study_Master_ID, 
C.Preclinical_Study_Master_Code_ID
FROM         PreclinicalStudyItems CC INNER JOIN
                      PreclinicalStudyMaster C ON CC.Preclinical_Study_Master_ID = C.Preclinical_Study_Master_ID
WHERE
(CC.Study_Template_Item = 'Number of Animals' AND 
 CC.Study_Template_Item_Value LIKE '%1%')

) 
TMP
GROUP BY
	Preclinical_Study_Master_ID
HAVING COUNT(Preclinical_Study_Master_Code_ID) = 3

Open in new window

0
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 250 total points
Comment Utility
Actually, if I understand it correctly, you would be better served with multiple inner joins. Something like:

SELECT Preclinical_Study_Master_ID, Preclinical_Study_Master_Code_ID
FROM PreclinicalStudyMaster p
INNER JOIN (SELECT Preclinical_Study_Master_ID FROM PreclinicalStudyMaster WHERE Study_Template_Item = 'Device Comments' AND Study_Template_Item_Value LIKE '%Device%') p1
ON p.Preclinical_Study_Master_ID=p1.Preclinical_Study_Master_ID
INNER JOIN (SELECT Preclinical_Study_Master_ID FROM PreclinicalStudyMaster WHERE Study_Template_Item = 'Director' AND Study_Template_Item_Value LIKE '%Dawn%') p2
ON p.Preclinical_Study_Master_ID=p2.Preclinical_Study_Master_ID
INNER JOIN (SELECT Preclinical_Study_Master_ID FROM PreclinicalStudyMaster WHERE Study_Template_Item = 'Number of Animals' AND Study_Template_Item_Value LIKE '%1%') p3
ON p.Preclinical_Study_Master_ID=p3.Preclinical_Study_Master_ID

etc, etc... basically, all you need to add is this part:
INNER JOIN (SELECT Preclinical_Study_Master_ID FROM PreclinicalStudyMaster WHERE Study_Template_Item = 'Device Comments' AND Study_Template_Item_Value LIKE '%Device%') p1
ON p.Preclinical_Study_Master_ID=p1.Preclinical_Study_Master_ID


This will return a single table which you can then work as you want. You can change the SELECT fields in the above query, add where clauses, inner join with other tables... This will make sure that you will have a table with just the records you want. The way it works is simple: Each inner join subquery will select one type of records. When you inner join to the original table p, you will therefore remove all IDs that don't have that type of record.
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
I object on one simple thing:
You shouldn't delete this question. If you think your answer is the best solution, then ask for a refund, but add your answer to the database, so other people will know what to do if they have a similar issue in the future.

And no one needs the points anyway :)
0
 

Author Comment

by:MedtronicVascSR
Comment Utility
Thanks Cluskitt. I don't believe I deleted anything. I left the checkbox "checked" where it read, "Add question to Knowledge Base".

Cheers,
Ty
0
 
LVL 18

Expert Comment

by:Cluskitt
Comment Utility
Well, the message said the question was about to be deleted in 4 days. You must have hit that instead. If you can't award your own answer, hit the request attention button on top and ask a mod for it. :)
0
 

Author Closing Comment

by:MedtronicVascSR
Comment Utility
Thanks again Cluskitt. Your alternative works too so I awarded you half the original points. In the end I decided to go with my solution only because it does not require creating and incrementing a dynamic alias for each potential "INNER JOIN" that reflects the number of search filters the user chooses.

Cheers,
Ty
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now