Hors Sujet Hors Sujet - 6 months ago 10
SQL Question

How do you get other tag to increase intersection constraint?

I have problem with query, who running in 2 seconds. Because subquery return 600 line, I need query without IN operator or faster query.

Currently, I have this query :

SELECT tag.idtag, tag, COUNT(*) AS nombre
FROM tag, questtag
WHERE tag.idtag = questtag.idtag
AND idquestion IN (
SELECT question.idquestion
FROM question, questtag
WHERE question.idquestion = questtag.idquestion AND idtag IN (1)
GROUP BY question.idquestion
HAVING COUNT(*) = 1
)
GROUP BY tag
ORDER BY nombre DESC, tag
LIMIT 0, 24


I want say to sql :
"Get other tag related with Javascript tag (intersection constraint) like stackoverflow."

I have 3 tables : question, questag and tag. The question and tag are related with questtag table.

Sql schema :






QUESTION (idquestion, question) :

1, "Javascript and Jquery"

2, "HTML, CSS, Javascript"

....




QUESTTAG (#idquestion, #idtag) :

1, 1

1, 2

2, 3

2, 4

2, 1

....




QUESTTAG (#idtag, tag) :

1, Javascript

2, Jquery

3, HTML

4, CSS

....




With Javascript, I have to get Jquery, HTML, CSS. With Javascript, HTML, I have to get CSS.

Answer

You might have better luck if you structured your query like this:

SELECT tag.idtag, tag, COUNT(*) AS nombre
FROM tag join questtag on tag.idtag = questtag.idtag
join (
      SELECT question.idquestion, COUNT(*) tags
      FROM question q2 join questtag qt2 on q2.idquestion = qt2.idquestion 
      AND idtag IN (1)
      GROUP BY question.idquestion
  )  temp on temp.idquestion = questtag.idquestion
WHERE tags = 1
GROUP BY tag.idtag, tag
ORDER BY nombre DESC, tag
LIMIT 0, 24

or you might not. In any event, it's worth a shot.