I'm trying to figure out if it is possible to combine two mysql queries into a single one. The first query returns results based on multiple selected tags. The second query returns a list of tags used in the database, ordered by their total count.
I have three tables, lets call them: - 'LIB' (contains all images, data, ect.) - 'TAGS' (contains all possible tags and their tag ids) - 'LIBTAGS' (contains records of all tags used in the lib, with LIB ids and TAG ids)
Example Tables:
LIB
---------------
id - 12345
desc - 'A description'
file - 'example.jpg'
thumb - 'exampleThumb.jpg'
TAGS
---------------
id - 3500
tag - 'exampletag'
LIBTAGS
--------------
libid - 12345
tagid - 3500
The first query looks like this:
SELECT from LIB l
JOIN LIBTAGS lt ON l.id = lt.libid
JOIN TAGS t ON lt.tagid = t.id
WHERE t.tag IN ('tag1', 'tag2', 'tag3') //varies with number of tags
GROUP BY l.id
HAVING count(DISTINCT t.tag) = 3 //varies with number of tags
This returns results from LIB containing all of the searched tags.
The second query looks like this:
SELECT lt.tagid, t.*, count(lt.tagid) AS cnt FROM LIBTAGS lt
JOIN TAGS t ON lt.tagid = t.id
GROUP BY lt.tagid
ORDER BY cnt DESC
This returns a list of the most used tags ordered by their total count.
My goal is to combine these and to return a list of tags (like above) but only for the results of the first query. Can this be done?
The purpose of this is to allow users to narrow their results by tags, and then present them with a list of tags which they can further narrow it down by.
The output I'm looking for is the ability to loop through results for the first query (from LIB), and an array of tags (or list I can make into an array) from the second query.
Any insight or advice is greatly appreciated. Thanks!