Solved

SQL query to use AND against all records in CSV Table

Posted on 2012-04-03
16
431 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

695 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