从一列或另一列获取用户ID的单行友谊数据库架构 [英] Single Row Friendship Database Schema with getting userid from one column or the other

问题描述

我需要帮助从表中查询friendID. 我的表存储了两个在一起成为朋友的成员的用户ID.

I need help querying the friendID from a table. My table stores the user id of two members who are friends together.

但是要存储两个成员的友谊"，我必须存储两个这样的记录:

But in order to store a "friendhship" b/w two members I would have to store two records like this:

friendshipID |  userID  |  friendID  
1            |  5       |  10
2            |  10      |   5

但是，当我们实际上只需要存储第一条记录就足够了，因为它包含两个成员的两个ID时，这对于数据库来说似乎很繁重.

Yet, that seems heavy for the DB when we really only need to store the first record as that is sufficient as it contains both ids of both members.

但是，当我要查询ID = 5的朋友的记录时，麻烦就来了.有时，该ID在userID列中，而其他时候则在friendID列中.

However, the trouble comes when I want to query the records of the friends of ID=5. Sometimes the ID is in the userID column and other times it is in the friendID column.

这是我正在使用的查询:

This is the query I am using:

SELECT *
FROM friends
WHERE userID = '5'
   OR friendID = '5'

但是我想做的就是这样

SELECT 
if $userID=5 then userID as myfriend
else friendID=5 then friendID as myfriend
FROM friends WHERE userID='5' OR myfriendID='5'

希望如此.最后，我希望拥有#5成员的所有朋友ID，而不要显示#5作为朋友或用户的结果....但仅显示他的朋友.

Hope that makes sense. In the end I would like to have all the friends ID's of member #5 and not bring up results with #5 as the friend or user....but just his friends.

推荐答案

此查询将返回#5朋友的ID值和名称，如此

This query would return the Id value, and name, of the friends of #5 as shown in this SQL Fiddle Example

SELECT f.FriendId AS FriendId
   , u.Name AS FriendName
FROM FriendTable AS f
INNER JOIN UserAccount AS u ON f.FriendId = u.UserId
WHERE f.UserId = 5

UNION

SELECT f.UserId AS FriendId
   , u.Name AS FriendName
FROM FriendTable AS f
INNER JOIN UserAccount AS u ON f.UserId = u.UserId
WHERE f.FriendId = 5

UNION将删除重复项，从而使该查询可用于单个记录的朋友，也可用于您在注释中提及的2条记录的友谊.不过，您不应该需要2条记录的友谊，因为第二条记录中没有存储新的信息，而只有一条记录是您无法获得的.

The UNION will remove duplicates, making this query work for both a single record of friends, or the 2 record friendship you mention in the comment. You shouldn't need the 2 record friendship though, because there is no new information being stored in the second record that you cannot get from only having one record.

这篇关于从一列或另一列获取用户ID的单行友谊数据库架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！