Data Explorer query to get my post with specific tag

Question

I would like to select all my posts with a specific tag with Data Explorer. Some posts may have multiple tags. If my specific tag is say powerbi I would like to select also all the posts that have powerbi and dax tags.

BTW is there any graph how the tables of Data Explorer are related?

Answer 1

This query does what you want

select p.id as [Post Link]
     , case when p.posttypeid = 1 then 'Q' else 'A' end [Q/A]
from posts p
inner join posts q 
        on q.id = coalesce(p.parentid, p.id) -- only questions have tags
where q.tags like '%<##tag?powerbi##>%'
and p.owneruserid = ##userid?1903793##
and p.posttypeid in (1,2)

Alternatively, if you want to select multiple tags:

select p.id as [Post Link]
     , case when p.posttypeid = 1 then 'Question' else 'Answer' end [Q/A]
from posts p
inner join posts q 
        on q.id = coalesce(p.parentid, p.id) -- only questions have tags
inner join posttags pt on pt.postid = q.id
inner join tags t on t.id = pt.tagid
cross join string_split(##tags:string?powerbi,dax##, ',') tv
where t.tagname = tv.value
and p.owneruserid = ##userid?1903793##
and p.posttypeid in (1,2)

A partial ERD can be found in Database Diagram of Stack Exchange model?

For the Data Dictionary see Database schema documentation for the public data dump and SEDE

Keep in mind SEDE is only updated once a week, on Sunday morning. If you're new to SEDE you might like the tutorial and if you have additional questions, feel free to hop in SEDE Chat