MYSQL QUERY with multiple options from a single table [duplicate]

Question

SELECT firstname, lastname, id 
FROM contact 
WHERE id IN 
      (SELECT contactid 
       FROM taglist 
       WHERE contactid IN 
           (SELECT contactid 
            FROM customerlist 
            WHERE companyid = 1) AND (tagid = 1 OR tagid = 4))

I have the query above where there is a single table that has a userid and a tag id. Each user can have multiple tags that are listed as separate entries on this table.

I need to be able to select only users with two or more specified tags. I thought the above query would do this, but it is returning every user with either tag 1 or tag 4.

If I change it to this: (tagid = 1 AND tagid = 4)) I get 0 results because it is only looking at each individual entry, and no entry has more than one tag per user.

This example only has two tags selected, but the goal here is to allow the end user to be able to select any number of available tags and only show results where the user matches all selected tags.

I don't have any way to know a specific number of tags that are going to be selected to match on. Currently there are 20 available tags, and clients can add their own tags if they wish.

So how do I accomplish this? I am very new to SQL queries, and not very familiar with joins yet so I am thinking this is going to have something to do with it, but I don't know. Any nudges in the right direction would be greatly appreciated.

In addition to filtering by tag, I am restricting the results to users in a particular company. — Belgarath the Ancient One
– Belgarath the Ancient One, Commented May 7, 2018 at 15:47

Gordon Linoff · Accepted Answer · 2018-05-07 15:30:02Z

0

You can get contacts with the two tags by doing:

SELECT contactid 
FROM taglist 
WHERE tagid IN (1, 4)
GROUP BY contactid
HAVING COUNT(*) = 2;

The rest of your query is doing other stuff not mentioned in your question, so it is unclear what you really want. Obviously, you can use this as a subquery to get full contact information.

answered May 7, 2018 at 15:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Belgarath the Ancient One Over a year ago

Sorry, while this example has two tags, I will need to have this work with multiple tags. Currently our system has around 12 different tags. The idea is that we could filter by any number of them.

Belgarath the Ancient One Over a year ago

actually, I think I can use this to fully resolve the issue, so thanks.

Collectives™ on Stack Overflow

MYSQL QUERY with multiple options from a single table [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related