将两列连接到来自不同表的一列

I am using SQL Server 2008. I have two sql queries

我正在使用SQL Server 2008。我有两个sql查询

select Course 
from StudentDB.dbo.Student 
where RollNo = 130

and

和

select Course 
from StudentDB.dbo.Courses 
where Course is not null

First query will return the following value

第一个查询将返回以下值

MSc

and the second query will return

第二个查询将返回

MCA
MSc
CSE
ECE
EEE

I need to join these queries and want an output like this

我需要加入这些查询，并需要这样的输出

MSc
MCA
CSE
ECE
EEE

That is appending the result of second query to the first query and removing the duplicate value.

将第二个查询的结果附加到第一个查询，并删除重复的值。

Please note I need result of first query on the top of the result. Here Msc is the first value because it is the result of first query. I tried union. But it returns the result of the second query itself. So union of those queries won't helps.

请注意，我需要第一个查询的结果在结果的顶部。这里Msc是第一个值，因为它是第一个查询的结果。我试着联盟。但是它返回第二个查询本身的结果。所以联合这些查询是没有用的。

3 个解决方案

#1

I'm not a fan of DISTINCT; working from JW's answer, this will give you the distinct list of values ordered with values from StudentDB.dbo.Student first:

我不喜欢与众不同;根据JW的答案，这将为您提供由StudentDB.dbo中的值排序的不同的值列表。学生第一次:

SELECT Course
FROM
(
    select Course, 1 ord from StudentDB.dbo.Student where RollNo = 130
    UNION
    select Course, 2 ord from StudentDB.dbo.Courses where Course is not null
) s
GROUP BY Course
ORDER BY min(ord)

#2

It could be because the default collation in your server is SQL_Latin1_General_CP1_CI_AS which means it is Case Insensitive (CI).

这可能是因为服务器中的默认排序规则是SQL_Latin1_General_CP1_CI_AS，这意味着它不区分大小写(CI)。

This makes your Msc be the same as MSC. You need to change your collation to a different one which is not case insensitive.

这使您的Msc与Msc相同。您需要将排序规则更改为另一个不区分大小写的排序规则。

See possible collations: http://msdn.microsoft.com/en-us/library/ms144250(v=sql.105).aspx

看到可能的针对性:http://msdn.microsoft.com/en-us/library/ms144250(v = sql.105). aspx

See usages of COLLATE http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/

查看COLLATE的用法:http://blog.sqlauthority.com/2007/04/30/case- sensibility -query-search/

#3

Just run this script in SQL server you will get the result, you may use it in store procedure also.

只要在SQL server中运行这个脚本，您就会得到结果，您也可以在存储过程中使用它。

SELECT Course,1 ord INTO #tmpTable FROM Student

INSERT #tmpTable
SELECT Course,2 ord FROM Courses WHERE Course IS NOT NULL 
AND Course NOT IN(SELECT Course FROM Student)

SELECT Course FROM #tmpTable ORDER BY ord

DROP TABLE #tmpTable

#1