CROSS JOIN in complicated query

Question

How can I cross join the result of this query with itself?

sql-fiddle: http://sqlfiddle.com/#!2/88f5e2/2

SELECT t.tid ,count(*) AS count
FROM taxonomy_index t 
JOIN taxonomy_term_data td ON td.tid = t.tid
WHERE t.created > UNIX_TIMESTAMP() - 3 * 86400 AND td.vid = 1
GROUP BY t.tid
ORDER BY count DESC
LIMIT 0, 5;

the query result is:

I need:

4429    4429       6
2634    4429       5
1703    4429       4
1742    4429       4
4468    4429       4
4429    2634       .
2634    2634       .
1703    2634       .
1742    2634
4468    2634
4429    1703
2634    1703
1703    1703
1742    1703
4468    1703
....        ....

For a start you you're limiting it to only 5 results, so remove your `LIMIT 0, 5` to start with. An SQL fiddle would be nice too so we actually know what we're working with — cameronjonesweb, Nov 26 '13 at 06:43
I don't think there is a way in MySQL without repeating the query. — , Nov 26 '13 at 07:07
reword your question: rather than trying to describe a solution, describe the problem. — zamnuts, Nov 26 '13 at 07:10
`select * from (... your original query ...) as t1 cross join (... your original query ...) as t2` — , Nov 26 '13 at 07:19

score 0 · Accepted Answer · edited May 23 '17 at 11:56

SELECT t_outer.tid as tid, t_inner.tid as tid_group, t_inner.count, t_inner.name
FROM taxonomy_index as t_outer
JOIN (
  SELECT t.tid ,count(*) AS count, td.name, td.vid
  FROM taxonomy_index t 
  JOIN taxonomy_term_data td ON td.tid = t.tid
  WHERE t.created > 0 AND td.vid = 1
  GROUP BY t.tid
) t_inner
WHERE t_outer.created > 0 AND t_inner.vid = 1
ORDER BY count DESC
LIMIT 0, 20;

yields:

TID     TID_GROUP   COUNT   NAME
4429    4429        3       A1
4468    4429        3       A1
4429    4429        3       A1
2634    4429        3       A1
4429    4429        3       A1
2634    4429        3       A1
4429    2634        2       B1
4468    2634        2       B1
4429    2634        2       B1
2634    2634        2       B1
4429    2634        2       B1
2634    2634        2       B1
2634    4468        1       C1
4429    4468        1       C1
4468    4468        1       C1
4429    4468        1       C1
2634    4468        1       C1
4429    4468        1       C1

http://sqlfiddle.com/#!2/88f5e2/29

credit where credit is due: count without group

updated: sorry, forgot to constrain the outer data. update 2: and i also forgot about the tid listing column

@MohammadAliAkbari how about now, is that what you're after? it must be late, i keep forgetting things... if it is not, please provide more data in the sqlfiddle b/c the relationships are not clear :( — zamnuts, Nov 26 '13 at 10:29

CROSS JOIN in complicated query

1 Answers1