Mysql secība trešajam join.

[Wuu]

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?

[jurchiks]

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.

[zeCode]

'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

[Kasspars]

Šī 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

[zeCode]

Šī problēma jārisina ar subselect

Updeitoju iepriekšējo selektu pirms paspēju tavu komentāru izlasīt

[Grey_Wolf]

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 !