单个查询从两个具有不同列的表中获取数据

I have two tables, event and performer and each of them has a slug column. In addition to the slug column, the event table has columns col1, col2 and col3 and the performer table has a column called id.

我有两个表，事件和表演者，每个表都有一个slug列。除了slug列之外，事件表还有列col1，col2和col3，而执行者表有一个名为id的列。

What I need is a single query that takes a slug value as input and looks at the event table first. If it finds a match, it returns all the columns from the event table. If it can't find a match, then it looks at the performer table and returns just the id.

我需要的是一个单独的查询，它将一个slug值作为输入并首先查看事件表。如果找到匹配项，则返回事件表中的所有列。如果找不到匹配项，那么它会查看执行者表并返回id。

In essence, it is the equivalent of the following two queries:

实质上，它相当于以下两个查询：

select col1, col2, col3 from event where slug = ?

If there are no results in the first query run the following:

如果第一个查询中没有结果，请运行以下命令：

select id from performer where slug = ?

I understand the the number of returned columns should be consistent so the value for id can be null in the first case and the values for col1, col2 and col3 can be null in the second case. I can test for null to see which was the case.

我理解返回列的数量应该是一致的，因此在第一种情况下id的值可以为null，在第二种情况下col1，col2和col3的值可以为null。我可以测试null，看看是哪种情况。

I would rather not have conditionals in the query - I have a feeling that it can be done with a single query, but can't figure out how.

我宁愿在查询中没有条件 - 我有一种感觉，它可以通过单个查询完成，但无法弄清楚如何。

1 个解决方案

#1

You could try the IF EXISTS which can all be done in one query. I know you said no conditionals, but this is an in query command.

您可以尝试IF EXISTS，它可以在一个查询中完成。我知道你说没有条件，但这是一个查询命令。

IF EXISTS(select col1, col2, col3 from event where slug = ?) 
BEGIN 
select col1, col2, col3 from event where slug = ?
END
ELSE 
BEGIN
select id from performer where slug = ?
END

I hope this helped!

我希望这有帮助！

#1