Question

表:

        users: id INT

        items: id INT
               setid INT

         sets: id INT

relationships: userid INT
               itemid INT
               relationship ENUM( owner ,  participant )

鉴于<条码>使用<>条码/代码>和<条码>setid,我们需要编制一份清单,列出所设计的所有物项,如果存在关系,则用户与每个项目的关系。一、结果

setid    itemid   relationship
---------------------------------
1        1        NULL
1        2        owner
....

The following don t work, as the second where 条款删除了<代码>relationship的行文。页: 1

select
    sets.id as setid,
    items.id as itemid,
    relatonships.relationship as relationship

from sets
    inner join items on sets.id = items.setid 
    left join relationships on relationships.itemid = items.id

where
    sets.id = 5
    and relationships.userid = 27

但是,第二个<代码>,条款删除了该项目与特定用户之间不存在现有关系的行文。如何以单一方式做到这一点?

Answer 1

很简单,我不理解你可以测试与加入......条款时的一贯性。

select
    sets.setid,
    items.itemid,
    relationships.relationship

from sets
    inner join items on items.setid = sets.id
    left join relationships
        on relationships.itemid = items.id
        and relationships.userid = 5
where
    sets.id = 1

Answer 2

www.un.org/Depts/DGACM/index_spanish.htm 你们通过尝试这一而要求做的不是100%。

select
    sets.id as setid,
    items.id as itemid,
    relatonships.relationship as relationship

from sets
    inner join items on sets.id = items.event 
    LEFT join relationships on relationships.itemid = items.id

where
    sets.id = [say, 5]
    and relationships.userid = [say, 27]

牢记,如果不存在关系一栏,关系一栏将是NUL。此外,《家庭与经济、社会、文化权利国际公约》条款中提及的关系也可以消除。希望能让你们开始,我再次会误解你试图实现的目标。从您的表格设计来看,用户与用户之间似乎没有关系,因此,可能的话,表格中的领域清单。

<><>>> 询问:

select sets.id as setId,
       items.id as ItemId,
       relationships.relationship
from sets
    join users on 1=1
    join items on sets.id=items.setid
    left join relationships on relationships.itemId = items.id
where 
    sets.id=5 and users.id = 27

<><>>> 通过将用户表与1=1相加,你不需要在什么地方提及所有<>>的关系表。

<><>>> 将左边改为:

left join relationships on relationships.itemId = items.id 
AND relationships.userId=users.id

友情链接