Solved

SQL query to use AND against all records in CSV Table

Posted on 2012-04-03
16
425 Views
Last Modified: 2012-04-20
Hi Experts,

I have an issue.  

I would like to be able to send a CSV list to a Stored Procedure, in order to get a list where the first query only has records that have Tags (DT_tblRelDocuments_Tags) that are in the EXISTS subquery

SELECT DISTINCT dt.intDocumentID, dt.vchDocumentDir, dt.vchFilename, dbo.getTagsCSV0(dt.intDocumentID) as Tags
FROM DT_tblDocuments dt 
INNER JOIN DT_tblRelDocuments_Tags trt ON dt.intDocumentID = trt.intDocumentID
WHERE dt.intCompanyID = 1
AND dt.intUserID = 1
AND EXISTS
	(SELECT *
	FROM DT_tblTags tt 
	INNER JOIN dbo.udf_CSVToTable('2007,May,hsbc,statement') csv ON tt.vchTag = csv.CsvValue
	WHERE tt.intTagID = trt.intTagID
	)

Open in new window

essentially the equivalent of something like this, which doesn't work
SELECT DISTINCT dt.intDocumentID, dt.vchDocumentDir, dt.vchFilename, dbo.getTagsCSV0(dt.intDocumentID) as Tags
FROM DT_tblDocuments dt 
INNER JOIN DT_tblRelDocuments_Tags trt ON dt.intDocumentID = trt.intDocumentID
INNER JOIN DT_tblTags tt ON tt.intTagID = trt.intTagID
WHERE dt.intCompanyID = 1
AND dt.intUserID = 1
AND tt.vchTag = '2007'
AND tt.vchTag = 'May'
AND tt.vchTag = 'hsbc'
AND tt.vchTag = 'statement'

Open in new window

0
Comment
Question by:Craig Lambie
  • 7
  • 4
  • 2
  • +2
16 Comments
 
LVL 19

Expert Comment

by:Rimvis
ID: 37804717
Hello cclambie,

How about this:

SELECT DISTINCT dt.intDocumentID, dt.vchDocumentDir, dt.vchFilename, dbo.getTagsCSV0(dt.intDocumentID) as Tags
FROM DT_tblDocuments dt 
	INNER JOIN DT_tblRelDocuments_Tags trt ON dt.intDocumentID = trt.intDocumentID
	INNER JOIN DT_tblTags tt ON tt.intTagID = trt.intTagID
	INNER JOIN dbo.udf_CSVToTable('2007,May,hsbc,statement') csv ON tt.vchTag = csv.CsvValue
WHERE dt.intCompanyID = 1 AND dt.intUserID = 1

Open in new window

0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 37804757
Nope... that essentially is giving me the "OR" or "IN" equivalent for the CSV list...

tblDocuments to TblRelDocuments_Tags is 1-M
TblRelDocuments_Tags to tblTags is 1-1

So essentially I need only tblDocuments rows where ALL the Tags are in the related table.
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 37804811
Something like this maybe. It's not tested on real table structure and data, but it might work:
SELECT DISTINCT dt.intDocumentID, dt.vchDocumentDir, dt.vchFilename, dbo.getTagsCSV0(dt.intDocumentID) as Tags
FROM DT_tblDocuments dt 
	INNER JOIN DT_tblRelDocuments_Tags trt ON dt.intDocumentID = trt.intDocumentID

WHERE dt.intCompanyID = 1 AND dt.intUserID = 1 
	AND NOT EXISTS(
		SELECT TOP 1 trt1.intTagID 
		FROM DT_tblRelDocuments_Tags AS trt1 INNER JOIN DT_tblTags tt ON tt.intTagID = trt.intTagID 
			LEFT OUTER JOIN dbo.udf_CSVToTable('2007,May,hsbc,statement') csv ON tt.vchTag = csv.CsvValue
		WHERE csv.CsvValue IS NULL	
		)

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 39

Expert Comment

by:appari
ID: 37804872
try this
;with csvtags as (Select * from dbo.udf_CSVToTable('2007,May,hsbc,statement') csv)
SELECT DISTINCT dt.intDocumentID, dt.vchDocumentDir, dt.vchFilename, dbo.getTagsCSV0(dt.intDocumentID) as Tags
FROM DT_tblDocuments dt 
INNER JOIN DT_tblRelDocuments_Tags trt ON dt.intDocumentID = trt.intDocumentID
WHERE dt.intCompanyID = 1
AND dt.intUserID = 1
AND (SELECT count(distinct tt.vchTag)
	FROM DT_tblTags tt 
	INNER JOIN csvtags ON tt.vchTag = csv.CsvValue
	WHERE tt.intTagID = trt.intTagID
	) = (Select count(*) from csvTags)

Open in new window

0
 
LVL 39

Expert Comment

by:appari
ID: 37804874
sorry missed the alias name, try this
;with csvtags as (Select * from dbo.udf_CSVToTable('2007,May,hsbc,statement') csv)
SELECT DISTINCT dt.intDocumentID, dt.vchDocumentDir, dt.vchFilename, dbo.getTagsCSV0(dt.intDocumentID) as Tags
FROM DT_tblDocuments dt 
INNER JOIN DT_tblRelDocuments_Tags trt ON dt.intDocumentID = trt.intDocumentID
WHERE dt.intCompanyID = 1
AND dt.intUserID = 1
AND (SELECT count(distinct tt.vchTag)
	FROM DT_tblTags tt 
	INNER JOIN csvtags csv ON tt.vchTag = csv.CsvValue
	WHERE tt.intTagID = trt.intTagID
	) = (Select count(*) from csvTags)

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37806909
SELECT DISTINCT dt.intDocumentID, dt.vchDocumentDir, dt.vchFilename, dbo.getTagsCSV0(dt.intDocumentID) as Tags
FROM DT_tblDocuments dt
INNER JOIN DT_tblRelDocuments_Tags trt ON dt.intDocumentID = trt.intDocumentID
INNER JOIN (
    SELECT intTagID
    FROM DT_tblTags
    WHERE vchTag IN ('2007', 'May', 'hsbc', 'statement')
    GROUP BY intTagID
    HAVING COUNT(DISTINCT vchTag) = 4
) AS tt ON tt.intTagID = trt.intTagID
WHERE dt.intCompanyID = 1
AND dt.intUserID = 1
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 37822449
Hi experts,
None of these solutions work.
Rimvis - all records that have any of the 4 tags
Scottpletcher, Appari - no records

Thoughts?
0
 
LVL 9

Expert Comment

by:keyu
ID: 37822547
SELECT DISTINCT dt.intDocumentID, dt.vchDocumentDir, dt.vchFilename, dbo.getTagsCSV0(dt.intDocumentID) as Tags
FROM DT_tblDocuments dt
INNER JOIN DT_tblRelDocuments_Tags trt ON dt.intDocumentID = trt.intDocumentID
INNER JOIN DT_tblTags tt ON tt.intTagID = trt.intTagID
WHERE dt.intCompanyID = 1
AND dt.intUserID = 1
AND tt.vchTag in('2007','May', 'hsbc','statement')
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 37822589
keyu? That is essentially a simplified version of my example.  I need one with AND instead of OR for the vchTag items.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 37823813
Hmm, if you used my last query, could it be possible that you don't have matching records in your source data?

Please run this by itself and see if you get results:

    SELECT intTagID
    FROM DT_tblTags
    WHERE vchTag IN ('2007', 'May', 'hsbc', 'statement')
    GROUP BY intTagID
    HAVING COUNT(DISTINCT vchTag) = 4
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 37826152
Hi ScottPletcher,

If I run your query I do get 0 results correct.
If I run it less the last line, I get 4 results.
Not sure what you are after here, could you maybe explain further so I can think about it too.

SELECT intTagID
FROM DT_tblTags
WHERE vchTag IN ('2007', 'May', 'hsbc', 'statement')
GROUP BY intTagID

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37827828
Then it looks like you don't have any matching IDs in that table.  That is, there is not a single id that has all four values.
0
 
LVL 1

Accepted Solution

by:
Craig Lambie earned 0 total points
ID: 37849972
Scott, that doesn't make any sense.
The table DT_tblTags cannot have a record with all 4 tags, but you have given me an idea....
(you where right....about "may" tag too, was not with the other 3 on any docs)

This works:
SELECT DISTINCT dt.intDocumentID, dt.vchDocumentDir, dt.vchFilename, dbo.getTagsCSV0(dt.intDocumentID) as Tags
FROM DT_tblDocuments dt 
WHERE dt.intDocumentID IN (
	SELECT trt.intDocumentID
	FROM DT_tblTags tt INNER JOIN DT_tblRelDocuments_Tags trt ON tt.intTagID = trt.intTagID
	WHERE tt.vchTag IN ('2007', 'hsbc', 'statement', 'November')
	GROUP BY trt.intDocumentID
	HAVING COUNT(DISTINCT tt.intTagID) >= 4
	)

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37851315
>> The table DT_tblTags cannot have a record with all 4 tags <<

I didn't say that.  I said one *ID* with all 4 tags.

Your final query is just a slightly different version of mine.

The join version will work also.  I think the advantage of the join version is that SQL can sometimes generate a better plan for a JOIN vs an IN.
0
 
LVL 1

Author Comment

by:Craig Lambie
ID: 37854258
Scott, you absolutely have to have the join, as the table in your query is simple the list of Tags, so no ID would ever have more than 1 tag, let alone 4, hence the need to actually completely change your query.  But it did lead me to the answer as I said, so thanks and full points to you.
Just to clarify further, the table "DT_tblRelDocuments_Tags" is where the many to many relationship is stored, hence the need to JOIN to it, the only reason to include the "DT_tblTags" table at all is so you can use text Tags instead of their IDs.
0
 
LVL 1

Author Closing Comment

by:Craig Lambie
ID: 37869849
Thanks for leading me down the right direction :)
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

829 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