在单个查询中从单个SQL Server表中进行条件SELECT/JOIN? [英] A conditional SELECT/JOIN from a single SQL Server table in a single query?

问题描述

即使在搜索后，我也在这里为正确类型的条件选择语句而苦苦挣扎.

I'm struggling with the right type of conditional select statement here, even after searching.

我与用户有一张桌子.有些是所有者，有些只是客人，就像这样:

I have a table with users. Some are owners, some are just guests, like so:

TABLE A 
UserID    | UserType | Directory | RegisteredToID | Name
1         | Guest    |           | 3              | Bob
2         | Guest    |           | 3              | Susan
3         | Owner    | abc       | (null)         | Charles
4         | Guest    |           | 20             | Hugh
etc...

我正在尝试获取一条查询语句，该语句生成Bob，Susan和Charles记录，并分别显示每个目录的名称，如下所示:

I'm trying to get one query statement that results the Bob, Susan, and Charles records with the directory name showing for each, like so:

Name    | Directory
Bob     | abc
Susan   | abc
Charles | abc

换句话说，鲍勃和苏珊是查尔斯的客人，但查尔斯的记录中包含我想要的目录名称.希望我的问题有意义吗?

In other words, Bob and Susan are guests of Charles, but Charles' record contains the directory name I want. Hope my question makes sense?

我应该已经提到了这一点…….我不知道目录名，只是一个用户ID(例如Bob的1)，并且正在尝试检索正确的目录名来.为每个用户关联，无论他们是否是访客.只有所有者记录才填写目录名称.

I should have mentioned this twist....I won't know the directory name, just a userID (e.g. 1 for Bob) and am trying to retrieve the correct directory name to associate for each user, whether they are a guest or not. Only owner records have a directory name filled in.

推荐答案

您需要自行连接表并使用

You need to join the table on itself and decide with CASE which Directory to select.

烦恼(目前无法访问SQL Server):

Out of my head (don't have access to a SQL Server at the moment):

SELECT
    t1.Name,
    CASE WHEN t1.RegisteredToID is null
        THEN t1.Directory 
        ELSE t2.Directory 
    END AS Directory
FROM 
    TableA AS t1
LEFT JOIN TableA AS t2 
    ON t1.RegisteredToID = t2.UserID

这篇关于在单个查询中从单个SQL Server表中进行条件SELECT/JOIN?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！