CODEWITHSUNDEEP
sqlsqlite
user3371963
user3371963

Reputation: 89

sql select column that exists in multiple selects. Select values that match all values in list

This select statement:

select * from favoritetags where tagid = 4

Returns 

ID |tagid | favoriteid |
12 |4     |12          |
50 |4     |42          |
42 |4     |34          |

Likewise the statement:

select * from favoritetags where tagid = 29

Returns:

ID |tagid | favoriteid |
49 |29    |41          |
51 |29    |34          |

I want to only get a list of favoriteid's that are in both.

Then I want to generalise this where I can get only those in rows that have faviriteid in common with a list of tagids. where tagid in (29,4,6) or any number of values.

Upvotes: 1

Views: 193

Answers (3)

forpas
forpas

Reputation: 164069

If you have the list in a comma separated string like: '4,29' then use it in this statement:

select favoriteid
from favoritetags
where ',' || '4,29' || ',' like '%,' || tag_id || ',%' 
group by favoriteid
having count(distinct tag_id) = length('4,29') - length(replace('4,29', ',', '')) + 1

So the statement is:

select favoriteid
from favoritetags
where ',' || ? || ',' like '%,' || tag_id || ',%' 
group by favoriteid
having count(distinct tag_id) = length(?) - length(replace(?, ',', '')) + 1

and you replace ? with your list.
See the demo.

Or you can do it with only 1 replacement by cross joining the list:

select f.favoriteid
from favoritetags f cross join (select ? list) t
where ',' || t.list|| ',' like '%,' || f.tag_id || ',%' 
group by f.favoriteid
having count(distinct f.tag_id) = length(t.list) - length(replace(t.list, ',', '')) + 1

See the demo.

If you want the values to query in a select statement then use a CTE with VALUES:

with list(tag) as (values (4), (29))
select favoriteid
from favoritetags
where tag_id in (select tag from list) 
group by favoriteid
having count(distinct tag_id) = (select count(*) from list)

See the demo.

Upvotes: 1

GMB
GMB

Reputation: 222422

You can use aggregation:

select favoriteid
from favoritetags
where tag_id in (4, 29)
group by favoriteid
having count(distinct tag_id) = 2

The where clause filters on tag_ids that belong to the wishlist. The query aggregates by favoriteid, and ensures that each favoriteid has two distinct tag_ids (this actually means that both 4 and 29 are available).

This can be easily extended for more tag_ids:

select favoriteid
from favoritetags
where tag_id in (4, 29, 6)
group by favoriteid
having count(distinct tag_id) = 3

Upvotes: 1

DCSQL777
DCSQL777

Reputation: 76

The following query gives all the tags that have more than one occurrence in the table.

 SELECT * FROM #tags T JOIN 
 (
     SELECT tagid,COUNT(*) AS [Count] FROM #tags
     GROUP BY tagid
     HAVING COUNT(*)>1
  )G
   ON t.tagid=g.tagid
   ORDER BY t.tagid

Upvotes: 2

Related Questions