MySQL apakšvaicājumi

[Jackal]

SELECT id, title, b.topics FROM forum_category AS a, (SELECT COUNT(id) AS topics, category AS category FROM forum_topic WHERE category = a.id) AS b

 

Parādās kļūda: Unknown column 'a.id' in 'where clause'

 

Kāpēc apakšvaicājumā nevar izmantot datus no citas tabulas, jeb es ko esmu līki uzrakstijis?

[Aleksejs]

Nekad nebūtu iedomājies tā rakstīt. Var jau būt, ka arī tā var, taču es rakstītu:

SELECT a.id, a.title, COUNT(b.id) AS topics
FROM forum_category AS a
LEFT JOIN forum_topic AS b
ON a.id = b.category

[Jackal]

Šāds variants neder, tagad tiek izvadīts tikai viens ieraksts no forum_category tabulas.

Ir divas tabulas forum_category (id, title), form_topic (id, category_id, title).

Mana doma bija ar vienu vaicājumu izvilkt visus forum_category tabulas ierakstus, katrai kategorijai saskaitīt topikus no forum_topic tabulas.

Edited September 14, 2009 by Jackal

[Aleksejs]

Hmmm... Man gan liekās, ka manis uzrakstītais tieši to arī dara:

Izdod visus tabulas forum_category ierakstus katram pieliekot šajā kategorijā esošo forum_topic ierakstu skaitu.

Vai tiešām nestrādā?

[Jackal]

Nop. forum_category tabulā ir divi ieraksti un forum_topic tabulā arī ir divi ieraksti (katrai kategorijai pa vienam).

Tavs dotais piemērs izvada tikai vienu kategoriju un saskaita visus forum_topic tabulas ierakstus neatkarīgi no tā kurai kategorijai ieraksts piesaistīts.

[Aleksejs]

Tātad... Ja man ir:

forum_category

id|title

1|zinas

2|notikumi

 

un:

forum_topic

id|category

1|1

2|2

 

Tad vaicājums izvada:

1|zinas|2

?

[Jackal]

Tieši tā

[Aleksejs]

Hmm...

un ja šitā:

SELECT a.id, a.title, COUNT(b.id) AS topics
FROM forum_category AS a
LEFT JOIN forum_topic AS b
ON a.id = b.category
GROUP BY b.category

[Jackal]

Strādā, paldies

[Jackal]

Negribējās taisīt jaunu tēmu, tāpēc jautašu tepat. Tagad mans vaicajums izskatas šadi:

 

SELECT a.id, a.title, a.description, COUNT(b.id) AS topics, b.title AS topic, COUNT(c.id) AS replays, c.date AS date, c.user AS user 
FROM forum_category AS a                                  
LEFT JOIN (forum_topic AS b)                 
ON (a.id = b.category AND b.status = '1')                                   
LEFT JOIN (forum_replay AS c)                
ON (b.id = c.topic AND b.status = '1')                                  
GROUP BY b.category

 

Kļūdas neparāda, bet ir vajadzība no forum_topic un forum_replay izvilkt ierakstu ar lielāko datumu. Mēģināju darīt šādi:

 

SELECT a.id, a.title, a.description, COUNT(b.id) AS topics, b.title AS topic, COUNT(c.id) AS replays, c.date AS date, c.user AS user 
FROM forum_category AS a                                  
LEFT JOIN (forum_topic AS b)                 
ON (a.id = b.category AND b.status = '1' ORDER BY b.date DESC)                                   
LEFT JOIN (forum_replay AS c)                
ON (b.id = c.topic AND b.status = '1' ORDER BY c.date DESC)                                  
GROUP BY b.category

 

bet tad parādās sintakses kļūda

Edited September 15, 2009 by Jackal