Question

Basically I have 4 tables and the group id of the current group. I ll list the important fields and info:

Users:
ID,
FirstName,
LastName

Groups:
ID,
participantlist_id

participantlist:
list_id

participantlist_links:
list_id,
participant_id

我已经指出,如何显示参与人名单上的所有用户。 list 在树顶点,但功能不大:

SELECT Users.ID,Users.FirstName,Users.LastName FROM Users
JOIN participantlist_links ON Users.ID = participantlist_links.participant_id
JOIN participantlist ON participantlist_links.list_id = participantlist.list_id
JOIN Groups ON participantlist.list_id = Groups.participantlist_id AND 
Groups.ID =  ".workspaces_tell_me_brainstorm_group_id_from_group_id($group_id)."

我现在要排除与参与人名单有关联的所有用户。清单——现组(组)——但我仅能以一个单一问题表明这样做的良好方式。我认为,解决办法将是某种左翼加入/右边或左边/右边同比。

我的临时工作,但非常昂贵的解决办法是:

SELECT table2.id as ID, table2.firstname as FirstName, table2.lastname as LastName
FROM

(SELECT table1.ID,table1.FirstName,table1.LastName FROM Users as table1
JOIN participantlist_links ON table1.ID = participantlist_links.participant_id
JOIN participantlist ON participantlist_links.list_id = participantlist.list_id
JOIN Groups ON participantlist.list_id = Groups.participantlist_id 
AND Groups.ID =  ".workspaces_tell_me_brainstorm_group_id_from_group_id($group_id)." )
as table2

LEFT JOIN
(SELECT table4.ID,table4.FirstName,table4.LastName FROM Users as table4
JOIN participantlist_links ON table4.ID = participantlist_links.participant_id
JOIN participantlist ON participantlist_links.list_id = participantlist.list_id
JOIN Groups ON participantlist.list_id = Groups.participantlist_id 
AND Groups.ID =  ".$group_id." ) 
as table3

ON table2.ID = table3.ID WHERE table3.id is null

Any suggestions?

Answer 1

我只谈一点一点简单。

我想不是 。它应当使你的法典更加容易阅读,而且最有可能让我的智慧更加优化。

SELECT table1.ID,
       table1.FirstName,
       table1.LastName 
  FROM Users as table1
  JOIN participantlist_links ON table1.ID = participantlist_links.participant_id
  JOIN participantlist ON participantlist_links.list_id = participantlist.list_id
  JOIN Groups ON participantlist.list_id = Groups.participantlist_id 
   AND Groups.ID =  ".workspaces_tell_me_brainstorm_group_id_from_group_id($group_id)." )
   AND table1.id not in (
               SELECT table4.ID
                 FROM Users as table4
                 JOIN participantlist_links ON table4.ID = participantlist_links.participant_id
                 JOIN participantlist ON participantlist_links.list_id = participantlist.list_id
                 JOIN Groups ON participantlist.list_id = Groups.participantlist_id 
                  AND Groups.ID =  ".$group_id." 
                )

友情链接