Hi I have the following Database structure:
嗨,我有以下数据库结构:
Table users contains : email, name
表用户包含:电子邮件,名称
table connections contains :followingemail, followedemail, isfollowedaccept
表连接包含:followingemail,followedemail,isfollowedaccept
table locations contains: location, email.
表位置包含:位置,电子邮件。
I Want to get the the name and the the location of the my friend when: I have the value of my Email which is followingemail and I have the value of my friend's email which is followedemail.
我希望得到我朋友的姓名和位置:我有我的电子邮件的价值,这是关注邮件,我有我的朋友的电子邮件的价值,这是followedemail。
It should retrieve the data only if I have a row in connctions table that contains my email asfollowingemail and my friend's email as followedemail and also isfollowedaccept is true.
只有当我在connctions表中有一行包含我的电子邮件asfollowingemail和我朋友的电子邮件作为followedemail并且isfollowedaccept为true时,它才应该检索数据。
My question is how can I write that query?
我的问题是如何编写该查询?
I can't figure it out. Thanks.
我无法弄清楚。谢谢。
2 个解决方案
#1
Try this:
select u.name, l.location
from connections C
join users U on U.followedemail = C.email
join location l on l.email = C.email
where C.followingemail = (your email)
and U.followedemail = (your friend's email)
and U.isfollowedaccept = true
#2
You need to join the users and locations 2 times each for getting the data, something as
您需要每次加入用户和位置2次才能获取数据
select
u1.name as following_name,
u2.name as followed_name,
l1.location as following_location,
l2.location as followed_location
from connections c
join users u1 on u1.email = c.followingemail
join users u2 on u2.email = c.followedemail
join locations l1 on l1.email = c.followingemail
join locations l2 on l2.email = c.followedemail
where
c.followingemail = '{your email}'
and c.followedemail = '{friends email}'
and c. isfollowedaccept = 'true'
#1
Try this:
select u.name, l.location
from connections C
join users U on U.followedemail = C.email
join location l on l.email = C.email
where C.followingemail = (your email)
and U.followedemail = (your friend's email)
and U.isfollowedaccept = true
#2
You need to join the users and locations 2 times each for getting the data, something as
您需要每次加入用户和位置2次才能获取数据
select
u1.name as following_name,
u2.name as followed_name,
l1.location as following_location,
l2.location as followed_location
from connections c
join users u1 on u1.email = c.followingemail
join users u2 on u2.email = c.followedemail
join locations l1 on l1.email = c.followingemail
join locations l2 on l2.email = c.followedemail
where
c.followingemail = '{your email}'
and c.followedemail = '{friends email}'
and c. isfollowedaccept = 'true'