Query to get Maximum unique combination

[tamilsweet]

Hello,
I'm trying to write a query to find maximum unique combination of two values. i.e. Posts with Tags.
This is for WordPress and if you know WordPress you need not look at the example otherwise here is what I looking for.

There are 4 tables with 1 example row. (showing only fields i'm interested in.)
terms
{ term_id, name }
10 | tag_1
term_taxonomy
{ term_taxonomy_id, term_id, taxonomy }
20 | 10 | 'post_tag'
term_taxonomy_relationships
{ object_id, term_taxonomy_id }
30 | 20
posts
{ post_id, title }
30 | post_1

post_id is the object_id

What I want is
post_id, post_title, name
and post_id and name should be unique.

Each post (post_id) can be associated with any no. of terms.
Example can be
post_id, post_title, associated terms
1 | post_1 | tag_1, tag_2, tag_3, tag_4
2 | post_2 | tag_1, tag_2, tag_3
3 | post_3 | tag_1, tag_2
4 | post_4 | tag_1

note that associated terms can be any combination, above is just an example.
Here is my query. (It could be the worst way to form query, all table have prefix wp_)


SELECT post_title, term_taxonomy_id, object_id FROM
(
SELECT post_title, object_id, term_taxonomy_id, count(*) as count
FROM wp_term_relationships
JOIN wp_posts ON object_id = ID WHERE term_taxonomy_id IN
(
SELECT term_taxonomy_id as id
FROM wp_term_taxonomy WHERE taxonomy = 'post_tag'
ORDER BY term_taxonomy_id DESC
)
AND post_type = 'post' AND post_status = 'publish'
GROUP BY term_taxonomy_id ORDER BY post_date
)
GROUP BY object_id
ORDER BY object_id DESC LIMIT 20


Any help would be really appreciated...

Regards,
Tamil

[tamilsweet]

I'm trying to provide a simplified version of the above question.

posts
post_id, title
1 | post_1
2 | post_2
3 | post_3
4 | post_4

tags (starting at 21 to avoid confusion with above table)
tag_id, name
21 | tag_1
22 | tag_2
23 | tag_3
24 | tag_4
25 | tag_5
26 | tag_6


post_tag_relation
post_id, tag_id
1 | 21
1 | 22
1 | 23
1 | 24
1 | 25
2 | 21
2 | 22
2 | 23
2 | 26
3 | 21
3 | 22
4 | 21
4 | 22


Combination.
1 | post_1 | tag_1, tag_2, tag_3, tag_4, tag_5
2 | post_2 | tag_1, tag_2, tag_3, tag_6
3 | post_3 | tag_1, tag_2
4 | post_4 | tag_1, tag_2

Unique combination with only 1 tag.
POSSIBILITY 1:
1 | post_1 | tag_1
2 | post_2 | tag_2

POSSIBILITY 2:
1 | post_1 | tag_4
2 | post_2 | tag_6
3 | post_3 | tag_1
4 | post_4 | tag_2

Here POSSIBILITY 2 has maximum rows. Imagine the tags table and posts tables having several hundred rows.

Looking for a query to fetch maximum rows.

Regards
Tamil