SQL2 query for accessing tags from assets

Question

I want to know what is the SQL2 query for accessing child nodes of a parent node for full text search.

I have an image saved into assets named men-watches.png. I have created a tag named watches and assigned this tag to asset mentioned above.

I have done the same with other images too. Please help me with the SQL2 full text search query as I want to search this asset.

I tried

select * from [dam:Asset] as d where contains (d.[jcr:content/metadata/cq:tags] = ‘watches’)

But I am asked to write a full text search query in SQL2 using IN clause for tags ‘watches’ and writing it for not only checking tags, but also for checking for title or description too.

Answer 1

Not sure if I understood it correctly, but this example would search if one of provided tags are on some images.

SELECT * FROM [dam:Asset] AS asset WHERE (asset.[jcr:content/metadata/cq:tags] IN ('watches','something else')) AND ISDESCENDANTNODE(asset,'/content/dam')