?
Solved

SQL query to use AND against all records in CSV Table

Posted on 2012-04-03
16
Medium Priority
?
447 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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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 70

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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 2000 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 70

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 70

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

850 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