Solved

SQL query to use AND against all records in CSV Table

Posted on 2012-04-03
16
419 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
 
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:ScottPletcher
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
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 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:ScottPletcher
ScottPletcher 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:ScottPletcher
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:ScottPletcher
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

911 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

17 Experts available now in Live!

Get 1:1 Help Now