Solved

SQL query to use AND against all records in CSV Table

Posted on 2012-04-03
16
417 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 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

23 Experts available now in Live!

Get 1:1 Help Now