Wuu Posted July 22, 2014 Report Share Posted July 22, 2014 mysqli_query($link,'select t1.link,t2.title,t2.starter_name,t2.start_date,t2.topic_firstpost,count(t1.uid) as coments,t3.members_display_name,"newcoments" as array_name from comments t1 left join '.$INFO['sql_tbl_prefix'].'topics t2 on (t1.link = t2.tid) left join '.$INFO['sql_tbl_prefix'].'members t3 on (t1.uid = t3.member_id) group by t1.link order by MAX(t1.id) desc limit 6;'); Problēma ir tu ka t3 on (t1.uid = t3.member_id) atgriež pirmo ierakstu t3.members_display_name, bet nepieciešams pēdējo pēc datuma, vai ID. Tiešām nevaru iebraukt kā panāk, jebkādu secības kārtošanu trešajam joinam. Un es skaidri saprotu kāpēc tiek atgriezts pirmais ieraksts, bet kā to var samainīt? Quote Link to comment Share on other sites More sharing options...
jurchiks Posted July 22, 2014 Report Share Posted July 22, 2014 mysqli_query($link, 'SELECT t1.link, COUNT(t1.uid) AS coments, t2.title, t2.starter_name, t2.start_date, t2.topic_firstpost, t3.members_display_name, "newcoments" AS array_name FROM comments t1 LEFT JOIN ' . $INFO['sql_tbl_prefix'] . 'topics t2 ON (t1.link = t2.tid) LEFT JOIN ' . $INFO['sql_tbl_prefix'] . 'members t3 ON (t1.uid = t3.member_id) GROUP BY t1.link ORDER BY MAX(t1.id) DESC LIMIT 6'); Lai pārējiem būtu vieglāk izlasīt tavu kveriju. Quote Link to comment Share on other sites More sharing options...
zeCode Posted July 22, 2014 Report Share Posted July 22, 2014 (edited) 'SELECT t1.link, COUNT(t1.uid) AS coments, t2.title, t2.starter_name, t2.start_date, t2.topic_firstpost, t3.members_display_name, "newcoments" AS array_name FROM comments t1, '.$INFO['sql_tbl_prefix'].'topics t2, '.$INFO['sql_tbl_prefix'].'members t3 WHERE t2.tid = t1.link AND t1.uid = t3.member_id AND t1.id = (SELECT MAX(t2.id) FROM comments t2 WHERE t2.link = t1.link) GROUP BY t1.link ORDER BY t1.id DESC LIMIT 6' Tīri bez LEFT JOINiem ... Varbūt strādā + t2 vietā vajadzētu izmantot citu aliasu (t4) Edited July 22, 2014 by zeCode Quote Link to comment Share on other sites More sharing options...
Kasspars Posted July 22, 2014 Report Share Posted July 22, 2014 Šī problēma jārisina ar subselect Izskatās šitā: SELECT * FROM table1 LEFT JOIN ( SELECT * FROM table2 ORDER BY field DESC ) table2 ON table2.id=table1.id Quote Link to comment Share on other sites More sharing options...
zeCode Posted July 22, 2014 Report Share Posted July 22, 2014 Šī problēma jārisina ar subselect Updeitoju iepriekšējo selektu pirms paspēju tavu komentāru izlasīt Quote Link to comment Share on other sites More sharing options...
Grey_Wolf Posted July 31, 2014 Report Share Posted July 31, 2014 Different SQL JOINs Before we continue with examples, we will list the types the different SQL JOINs you can use: INNER JOIN: Returns all rows when there is at least one match in BOTH tables LEFT JOIN: Return all rows from the left table, and the matched rows from the right table RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table FULL JOIN: Return all rows when there is a match in ONE of the tables --------------------- http://www.w3schools.com/sql/sql_join.asp tavā gadījumā nemaz nevajag pilno tabulu apvienojumu ! tas tikai noslogo serveri, un neļauj pareizi sakārtot datus .. P.S. nu jā nākamreiz kad posto kveriju, saliec viņu tā lai var izlasīt ! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.