March 29, 2007 at 9:45 am
I have the following simple select query:
SELECT DISTINCT Job.JobID, Job.DateAdded
FROM Job, JobTag, Image, Tag, TagCat
WHERE TagCat.TagCatID = Tag.TagCatID AND Tag.TagID = JobTag.TagID AND JobTag.JobID = Job.JobID AND Image.JobID = Job.JobID
AND JobTag.TagID IN (#TagIDList#)
ORDER BY Job.DateAdded DESC
However, I need to add to the query the following:
The TagIDs have to fall into particular categories with an AND operator and be OR'ed with the other categories.
That's a really bad explanation of what I need to do, but I hope someone can at least understand what I'm trying to ask and show me an example of how the syntax might look for this. I imagine that I can add a subquery within the IN operator expression, but I'm not sure if that's the right approach or not.
March 29, 2007 at 10:42 am
Maybe you can give an example with data? Not really sure what you're looking for.
March 29, 2007 at 11:39 am
Alright, here's a query with some sample input and output that might clarify what I need to do:
SELECT DISTINCT Job.JobID, JobTag.TagID, TagCat.TagCatID
FROM Job, JobTag, Image, Tag, TagCat
WHERE TagCat.TagCatID = Tag.TagCatID AND Tag.TagID = JobTag.TagID AND JobTag.JobID = Job.JobID AND Image.JobID = Job.JobID
AND JobTag.TagID IN (35,46,51,4)
ORDER By Job.JobID
And, it returns this:
JobID TagID TagCatID
194358
194462
194517
195358
195462
212358
221358
222416
222358
222462
222517
223358
223462
225462
226358
226517
227358
227462
228416
228358
What I really want this query to do is to only select JobID 222 because it has tagids which are in all the Tag Categories.
March 30, 2007 at 10:33 am
/*Use the count of Tag Ids your looking for, run your original query into a temp table and.. */
DELETE FROM #temp
WHERE JobID IN
(SELECT JobID from #temp
GROUP BY JobID
HAVING COUNT(*) < @TagIdCount) -- here @TagIDCount = 4
/*You shouldn't have anyting but 222 left from your example data */
March 30, 2007 at 11:22 am
Without example source data it is difficult to tell what you want.
You may want something along the lines of:
-- Set up Tag List
DECLARE @TagList TABLE
(
TagID int NOT NULL
)
INSERT INTO @TagList
SELECT 35 UNION ALL
SELECT 46 UNION ALL
SELECT 51 UNION ALL
SELECT 4
-- Get Tag Count
DECLARE @TagCount int
SELECT @TagCount = COUNT(*)
FROM @TagList
SELECT J.JobID, J.DateAdded
FROM Job J
JOIN (
SELECT DISTINCT D1.JobID
FROM (
SELECT D2.JobID, D2.TagID, D2.TagCatID
FROM (
SELECT DISTINCT
JT.JobID, JT.TagID, TC.TagCatID
FROM JobTag JT
JOIN Tag T
ON JT.TagID = T.TagID
JOIN @TagList TL
ON JT.TagID = TL.TagID
) D2
GROUP BY D2.JobID, D2.TagID, D2.TagCatID
HAVING COUNT(*) = @TagCount
) D1
) D
ON J.JobID = D.JobID
ORDER BY J.DateAdded DESC
April 4, 2007 at 2:52 pm
I think this is very close to what I need, but is there anyway to make this one query instead of two?
April 4, 2007 at 4:34 pm
Ken, that looks close. I've added on line that you left out that joins the TagCat table.
DECLARE @TagList TABLE
(
TagID int NOT NULL
)
INSERT INTO @TagList
SELECT 35 UNION ALL
SELECT 46 UNION ALL
SELECT 51 UNION ALL
SELECT 4
-- Get Tag Count
DECLARE @TagCount int
SELECT @TagCount = COUNT(*)
FROM @TagList
SELECT J.JobID, J.DateAdded
FROM Job J
JOIN (
SELECT DISTINCT D1.JobID
FROM (
SELECT D2.JobID, D2.TagID, D2.TagCatID
FROM (
SELECT DISTINCT
JT.JobID, JT.TagID, TC.TagCatID
FROM JobTag JT
JOIN Tag T
ON JT.TagID = T.TagID
JOIN TagCat TC
ON TC.TagCatID = T.TagCatID
JOIN @TagList TL
ON JT.TagID = TL.TagID
) D2
GROUP BY D2.JobID, D2.TagID, D2.TagCatID
HAVING COUNT(*) = @TagCount
) D1
) D
ON J.JobID = D.JobID
ORDER BY J.DateAdded DESC
When I run this query, it does not return any records.
April 4, 2007 at 4:41 pm
Maybe it would help for you to see what a couple of the columns in the tag table look like:
TagCatID TagID
227
246
247
248
249
250
356
357
358
462
463
464
465
466
467
574
575
576
577
578
668
669
670
671
672
673
751
752
753
754
755
835
882
162
164
167
168
1623
1625
1626
1629
1632
1633
1634
1636
1728
1730
1731
1737
1738
1739
1740
1741
1742
1783
1986
1987
1990
1991
1992
1993
1994
1995
1996
19101
19105
19109
19111
19113
20117
20118
20119
20120
20123
20124
20129
20130
20131
20133
20138
20140
20142
20147
April 5, 2007 at 3:33 am
Sean and I have given you outline solutions to your problem.
Without detailed information on your schema, and its data, it is difficult to give you more help.
You should now be able to work this out yourself. If you want more help please follow these guidelines:
http://www.aspfaq.com/etiquette.asp?id=5006
April 6, 2007 at 10:20 am
I think I finally have what I want. This query seems to work:
SELECT j.jobid
FROM Job j
JOIN Image i
ON i.jobid = j.jobid
JOIN ImageTag it
ON it.ImageID = i.ImageID
JOIN Tag t
ON t.tagid = it.tagid
JOIN TagCat tc
ON tc.tagcatid = t.tagcatid
AND it.tagid IN (51,35,46,27,47,16,113,8,4,71,93,57,33)
WHERE i.DisplayImage = 'True'
GROUP
BY j.jobid
HAVING COUNT(distinct tc.TagCatID) >= (SELECT Count(DISTINCT t.tagcatid) FROM tagcat tc, tag t WHERE tc.TagCatID = t.TagCatID AND t.tagid IN (51,35,46,27,47,16,113,8,4,71,93,57,33)
)
Does it make a difference if I put the AND under the WHERE clause?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply