Jump to content
php.lv forumi

Mysql secība trešajam join.


Wuu

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

'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 by zeCode
Link to comment
Share on other sites

  • 2 weeks later...

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 !

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...