我是MySQL的初学者,我在MySQL中有一些课程,现在已经完成了,我有一项由我们老师提供的家庭作业,可以进行培训,但我阻止了检索数据来自社交网站的基础。我了解加入的基础,但我仍然很难理解左JOIN或其他类型的内在加入...
这是我的问题,我有一个带有2个表的数据库,
member(id_member*, login, photo)
friend(id_member_request*, id_member_accept*, accept, date_acceptation)
朋友表的接受字段是一个字段,可以通过将值设置为1而不是0来验证他们是否有朋友。
字段id_member_request和id_member_accept同意成员表的id_member。
我想检索朋友的登录和成员的图片,以便能够显示它们。
我测试了几个查询:
SELECT m.login
, m.photo
FROM friend AS a
LEFT
JOIN member AS m
ON m.id_member = a.id_member_accept
LEFT
JOIN member AS m1
ON m1.id_member = a.id_member_request
WHERE accept = 1;
查询有效,但没有显示所有友好成员,甚至几次。
右加入结果为null。在内部连接中没有结果。
事先感谢,因为我封锁了几个小时,我承认要失去一点。; - )
格式化语句将帮助您:)好的,所以您正在加入M和M1上的同一列。
以下将带回数据,但是您必须传递ID_Member_Request ..
DECLARE INT @MEMBERID = 2; --Example ID
SELECT m.login, m.photo
FROM friend AS a
LEFT JOIN member AS m ON m.id_member = a.id_member_accept
WHERE a.id_member_request = @MEMBERID
AND a.accept = 1;
这是带有M.login,M.Photo的所有朋友,以@Memberid;
我想您想要登录的人的朋友。对于特定的会员_id,这里的查询是可以帮助您的
select * from friend a
inner join member b on (a.id_member_request=b.id_member or a.id_member_accept=b.id_member) and b.id_member=1
where accept=1
其中1是成员ID,其朋友将显示
您可以像以下示例中重写查询
SELECT `m`.`id_member` AS `memberID`,
`m`.`login` AS `memberLoginName`,
`m`.`photo` AS `memberPhoto`,
`m1`.`id_member` AS `friendID`,
`m1`.`login` AS `friendLoginName`,
`m1`.`photo` AS `friendPhoto`,
`a`.`accept_date` AS `acceptDate`
FROM `friends` AS `a`
INNER JOIN `member` AS `m` ON `a`.`id_member_request` = `m`.`id_member`
INNER JOIN `member` AS `m1` ON `a`.`id_member_accept` = `m1`.`id_member`
WHERE `a`.`accept`=1
ORDER BY `a`.`id_member_request`,`a`.`id_member_accept` ASC
在SQL小提琴中查看此示例
非常感谢您的帮助; - )
我已经尝试了您的所有答案,并且有了一点更改,即使在内部,左,右加入...
也可以正常工作。SELECT login,photo
FROM friend AS a
INNER JOIN member AS m ON ( a.id_member_request = m.id_member
OR a.id_member_accept = m.id_member )
WHERE m.id_member !=$id_member
AND a.accept=1;