Php mysql查询来自两个表的数据

i have two tables in my database. table A is USERS and table B is relations, and the following are their columns USERS(username, avatar, specialty) and RELATIONS(username1, username2, reldir) RELATIONS stores the relationship between users, that is if, username1 is following username2, reldir = F, and if they are both following each other, reldir=FB and vice versa, this part has worked very well but

我的数据库中有两个表。表A是USERS,表B是关系,以下是它们的列USERS(用户名,头像,专业)和RELATIONS(username1,username2,reldir)RELATIONS存储用户之间的关系,即if,username1跟随username2,reldir = F,如果它们彼此跟随,reldir = FB,反之亦然,这部分工作得很好但是

i need to query these tables so that i return a list of users from USERS which for example user A doesnt follow but have the same specialty as A...

我需要查询这些表,以便我返回USERS的用户列表,例如用户A不遵循但具有与A相同的专业...

i tried this, but its not working well ...

我试过这个,但效果不好......

$spec = the specialty of user A

$ spec =用户A的专长

SELECT a.username, a.avatar, a.specialty FROM users a, relations b WHERE a.username!=b.username2 AND (b.reldir!='F' OR b.reldir!='FB') AND a.speciality ='$spec'

the query to me seems logically correct but i could be wrong. i need help

对我的询问似乎在逻辑上是正确的,但我可能是错的。我需要帮助

1 个解决方案

#1

You need to add some keys for your tables, because you have two different tables and they doesn't linked.

您需要为表添加一些键,因为您有两个不同的表,并且它们没有链接。

For example, table USERS:

例如,表USERS:

id (as primary_key), username, avatar, specialty

Table RELATIONS:

user_id, username2, reldir

user_id - it is field "id" from table USERS (instead your "username1")

user_id - 来自表USERS的字段“id”(而不是您的“username1”)

Then you will be able create a query like this:

然后你就可以创建一个这样的查询:

SELECT a.* 
FROM users a, relations b 
WHERE a.id = b.users_id 
  AND (b.reldir != 'F' OR b.reldir != 'FB') 
  AND a.speciality = '$spec'

ps: if I understood your question in the right way)

ps:如果我以正确的方式理解你的问题)

#1