SQL Query使用Sql join和没有子查询来获取结果表 [英] SQL Query to get result table using Sql join's and without sub query

问题描述

我需要一个查询来从两个表中获取值，如下所示。请有人帮我。

I need a query to get values from two table as shown below. Please somebody help me on the same.

City table is below
tblLocation 
Id   CityName
1    Bangalore
2    Mysore
3    Hassan
4    Chennai
5    Hyderabad

Route table is below
tblRoute   
Id  SourcePlace DestinationPlace
1   1           2
2   3           4
3   5           1
4   3           4
5   5           1
6   2           4

Result table is below
RouteId  SOurcePlace    DestinationPlace
1        Bangalore      Mysore
2        Hassan         Chennai
3        Hyderabad      Bangalore
4        Hassan         Chennai
5        Hyderabad      Bangalore
6        Mysore         Chennai

我试图获得的查询结果表格如下：

The query I tried to get above Result table is as below

select r.Id as RouteId,
(select CityName from tblLocation where ID=r.SourcePlace) as SourcePlace,
(select CityName from tblLocation where ID=r.DestinationPlace) as DestinationPlace
 from tblRoute

以上查询效果是减。所以请提供哪些性能好的查询。也不要使用上面的内部查询。



提前谢谢..： - ）

Above query performance is less. So please provide query which performance good. Also don't use inner queries like above.

Thanks in advance.. :-)

推荐答案

您必须两次加入tblLocation表。这看起来像

You must JOIN the tblLocation table two times. That looks something like

SELECT tblRoute.Id as RouteId, a.Cityname as SourcePlace, b.Cityname as DestinationPlace
FROM tblRoute
INNER JOIN tblCity a ON tblRoute.SourcePlace=a.Id
INNER JOIN tblCity b ON tblRoute.DestinationPlace=b.Id

SELECT a.Id as RouteId, b.Cityname as SourcePlace, c.Cityname as DestinationPlace
from tblRoute a inner join tblLocation b on a.SourcePlace = b.Id
left outer join tblLocation c on a.DestinationPlace = c.Id

在SQL Server上测试 - 2008 R2 ，它工作正常。

Tested it on SQL Server - 2008 R2, It works fine.

这篇关于SQL Query使用Sql join和没有子查询来获取结果表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！