Forum PHP Romania - Discutii despre PHP, MySQL, Javascript, AJAX, etc

[alin2005]

Salut,

Cum pot selecta un "count" dintr-un query unde sunt Join-uri multiple fara sa folosesc "GROUP BY" pt ca am nevoie de "ORDER BY"

Query-ul:
Cod: $res = mysql_query(
"SELECT at.postid, t.subject, COUNT(p.id) AS p_id ".
"FROM attachments AS at ".
"LEFT JOIN attachmentdownloads AS atdl ON at.filename=atdl.filename ".
"LEFT JOIN topics AS t ON at.topicid=t.id ".
"LEFT JOIN posts AS p ON p.topicid=at.topicid ".
"WHERE at.owner=".sqlesc($CURUSER['id'])." ORDER BY at.filename ASC"
) or sqlerr(__FILE__, __LINE__);

am nevoie de COUNT id din tabelul posts, dar fara GROUP BY, deoarece cum am zis am nevoie de ORDER BY ca sa fie in regula :(

Daca folosesc ORDER BY imi da eroarea asta

Citat: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause in...

PS: am incercat cu COUNT(DISTINCT p.id) AS p_id si tot nimic

Multumesc anticipat :)

[Quber]

Nustiu daca e correct dar incearca asha:

Cod:
$res = mysql_query(
"SELECT at.postid, t.subject, COUNT(p.id) AS p_id ".
"FROM attachments AS at, posts AS p ".
"LEFT JOIN attachmentdownloads AS atdl ON at.filename=atdl.filename ".
"LEFT JOIN topics AS t ON at.topicid=t.id ".
"WHERE p.topicid=at.topicid  AND at.owner=".sqlesc($CURUSER['id'])." ORDER BY at.filename ASC"
) or sqlerr(__FILE__, __LINE__);

[alin2005]

din pacate nu merge...

tot aceiasi eroare...

:(

[carco]

foloseste GROUP BY (si poti pune si ORDER)

[alin2005]

am incercat si asa, dar nu trebuie sa am group by, ca altfel modifica ce vreau eu, le groupeaza si nu imi arata realele "rows" :(

[dechim]

Daca n-ai clauza GROUP BY n-are count(*) ce sa contorizeze.
As zice sa incerci sa adaugi la sfarsit un UNION dintr-un select asemanator dar care sa faca GROUP BY si count(), mai ai astfel un set de inregistrari cu cheia si count() (din aceaste inregistrare campurile in plus nu intereseaza)
Asa ma gandesc:
Cod:

SELECT at.postid AS post_id, t.subject AS subject_name, 0 AS p_id, alte_campuri
   FROM attachments AS at
   LEFT JOIN attachmentdownloads AS atdl ON at.filename = atdl.filename
   LEFT JOIN topics AS t ON at.topicid = t.id
   LEFT JOIN posts AS p ON p.topicid = at.topicid
   WHERE at.owner=$id 
   UNION
(
SELECT
   at.postid AS post_id, t.subject AS subject_name, count(*) AS p_id , alte _campuri
   FROM attachments AS at
   LEFT JOIN attachmentdownloads AS atdl ON at.filename = atdl.filename
   LEFT JOIN topics AS t ON at.topicid = t.id
   LEFT JOIN posts AS p ON p.topicid = at.topicid
   WHERE at.owner=$id 
   GROUP BY at.postid
)   
   ORDER BY at.filename, p_id ASC

[alin2005]

Citat: Table 'at' from one of the SELECTs cannot be used in global ORDER clause in

[carco]

sorry, nu vazusem ce vrei sa faci, m-a indus in eroare afirmatia ca nu merge group cu order

incearca in loc de COUNT(p.id) AS p_id sa folosesti un subquery
Cod:
SELECT
  at.postid,
   t.subject,
  (select count(*) FROM posts WHERE posts.topicid = at.topicid) AS p_id
FROM  attachments AS at
...

(si nu mai pui LEFT JOIN posts AS p ON p.topicid=at.topicid)

[alin2005]

Mersi mult carco, asa a mers, nu stiam de asta :)

:P

[PHPRomania Bot]