Link to home
Start Free TrialLog in
Avatar of Hynne
Hynne

asked on

Finding rows with the same goup of related values

I hope someone can help me with this problem. I have had big problems, just describing the problem, but here goes:

I have three tables: Notes, Tags, and Note_Tags, the last giving me a many-to-many relation between the two first tables. I use SQL Server 2008

I need to find all Notes which have a selection of tags in common (I must call this query from c#, but I would prefer not to create a dynamic query).

What I need is something like the following code, but it should handle an unspecified number of Tag_Id values:


select a.Note_Id
from (
	select Note_Id
	from Note_Tag
	where Tag_Id = 6) a
inner join (
	select Note_Id
	from Note_Tag
	where Tag_Id = 8) b on a.Note_Id=b.Note_Id

Open in new window

Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

select Note_ID from Note_Tag where Tag_ID in (6,8)

you can build this query in code, specifying the list of values for the In clause as a set of values, separated by commas

You would pas the list of values as a Simple string, which would need to be parsed into a set of individual values, separated by commas - this would be a user-defined function.

AW


ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Not sure if 10 is really a valid ID, but wanted to show that the number of values doesn't matter.  You use the IN statement to give you the flexibility to check on multiple tag_id.  The problem is that you can have a Note that only matches one of the 2 or 3 or more and my understanding is that you want the Notes that match all the values at once.

Therefore, if you group by the note id and count the distinct tag ids then you can make sure that it has exactly the number of tags you are checking on.  If it doesn't then it doesn't fit your criteria.

Regards,
Kevin
Hi,

I think the above query will return all Note_IDs that have either 6 or 8 as Tag_IDs, and as I understand the question, Hynne is looking for just those NoteIDs that have both 6 and 8 as TagIDs.

How about something along these lines:

SELECT DISTINCT NoteTags.NoteID
FROM NoteTags
WHERE NoteTags.TagID IN (6,8)
GROUP BY NoteTags.NoteID
HAVING Count(NoteTags.NoteID)=2;

(The number of the Count needs to match the number of items in the IN list.)
Drat, too slow! My previous comment was referring to Arthur Wood's initial response - I hadn't spotted mwvisa1's post at that point.
Avatar of jamesgu
jamesgu

select Note_Id
        from Note_Tag
group by note_id
having count(*) > 1

Avatar of Hynne

ASKER

Ahh... I must have been tired this morning as well... I just now saw that my solution is a complete ripoff of Calpurnia's solution :-).
Is there any way I can give Calpurnia points as well?
Actually, I suggested it first much earlier as Calpurnia pointed out, so you could request the question be unaccepted and then you can relook at the solutions.

You asked for Tag_Id to be in a specific list and each note much match a number of these tags in conjunction with each other.  The solution you selected will not do that.  It will just tell you which Notes have more than one tag_id.  Each note in that list can have totally different tag_id like one can match to id 1 and 3 and the other 6 and 8.  Since they both have a count of tags > 1, they both show in the list.  Is that what you intended?

Anyway, think you can use the request attention button which will automatically post a question to the zone advisor and you can ask for the question to be re-opened; then you can assign points again.
Avatar of Hynne

ASKER

Thanks for the answer. I can see from the responses, that I really didn't have any luck explaining what my problem was. (I'm sorry, but I was getting a bit tired :-)), so I'll try again, and supply the solution I have decided on, which was inspired by this answer (I need to check that COUNT(*) = the number of tags):

I needed to find Notes which had ALL the supplied tags in common.
If e.g. I wanted Notes with the tags 4 and 5 and the relations where like this:
Note      Tags
A                  1, 3, 4
B                  2, 3, 4, 5
C                  1, 4, 5
D                  3, 4
then the query should return Notes B and C.

I found this solution, which I implemented as a stored procedure:
      select Note_Id
      from Note_Tag
      where Tag_Id in (select Id from @InList)
      group by
            n.Note_Id
      having COUNT(*) = (select COUNT(*) from @InList)

I call this stored proc with @InList as a table variable filled with the Tags