从多个表中选择值 - SQL

时间:2021-08-11 21:42:16

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'