在sql server 2005中遇到了内连接问题 [英] got stuck with inner join in sql server 2005

问题描述

我有一个2表即tblSchedules和tblCity，在tblSchedules中我有两列名为'source'和'Destination'。

两者都有一个关系tblcity表到同一列cityID，如何使用内连接编写查询。请指导我

i have a 2 table namely "tblSchedules" and "tblCity",In "tblSchedules" i have two columns with name 'source' and 'Destination'.
Both have a relation tblcity table to same column cityID ,how to write query using inner join .please guide me

推荐答案

尝试：

SELECT s.*, 
       c.* 
FROM tblSchedules s
INNER JOIN tbtCity c
ON s.cityID = c.cityID

Markdown偷走了星号！

如果你仍然看不到它们，那么SELECT和FROM之间应该是一个s。和c。两个后跟一个*

[/ edit]







如果您具体，这会有所帮助！ ：笑：

你需要为要用城市名称替换的时间表中的每一列加入：

[edit]
"Markdown" stole the asterisks!
If you still can't see them, between the SELECT and the FROM should be an "s." and a "c." both followed by a "*"
[/edit]


[edit]
It helps if you are specific! :laugh:
You need a JOIN for each column in the schedules you want to replace with the city name:

SELECT s.ScheduleId, s.FlightId, c1.CityName AS [From], c2.CityName AS [To], s.Departure_Time, s.Arrival_Time, s.[Date]
FROM tblSchedules s
INNER JOIN tblCities  c1
ON c1.CityID = s.Source
INNER JOIN tblCities c2
ON c2.CityID = s.Destination

会给你：

Will give you:

ScheduleId FlightId     From       To Departure_Time Arrival_Time      Date
         4        1   Mysore   Mumbai        5:30 PM     10:20 AM  5/9/2015

但有几条建议：转发三栏Departure_Time ，Arrival_Time和Date并用两个DATETIME字段替换它们：DepartAt和ArriveAt - 分别存储日期和时间意味着使用字符串，这会导致以后的各种问题。使用DateTime值意味着您可以更轻松地对它们进行数学运算，搜索和排序。 （它表明飞机没有及时返回，就像你的插图一样！：笑:)：



[/ edit]

A couple of suggestions though: Dump the three columns "Departure_Time", "Arrival_Time", and "Date" and replace them with two DATETIME fields: "DepartAt" and "ArriveAt" - storing dates and times separately means using strings, and that causes all sorts of problems later on. Using DateTime values means you can do math, searching, and sorting on them a lot easier. (And it shows that the aircraft doesn't travel back in time, as it seems from your illustration! :laugh: )

[/edit]

请忽略这个解决方案！

这是向Chris展示我对错误报告的意思：



那里对于解决方案，没有禁用降价格式。

在SQL块中：

PLEASE IGNORE THIS SOLUTION!
It's here to show Chris what I mean with a bug report:

There is no "disable markdown formatting" for solutions.
Within an SQL block:

SELECT s.*, c.*
FROM

没有*字符

但是在不同的行上：

There are no "*" characters
But on separate lines:

SELECT s.*, 
       c.*
FROM

它们出现。

除了 OriginalGriff [ ^ ]，我建议阅读：视觉表现SQL联接 [ ^ 。注意：至少有几种类型的连接。

In addition to solution 1 by OriginalGriff[^], i'd suggest to read this: Visual Representation of SQL Joins[^]. Note: there's at least few types of join.

这篇关于在sql server 2005中遇到了内连接问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！