Mixing
how to get data from SQL server ToCityId And FromCityId Bases
-1
I have below query I want to show data fromCityId AND ToCityId . Suppose passenger travel fromCity London toCity Manchester. How do I write query like this, when I use where clause and in, its show me same values in fromcity and tocity
Expected results are in below Picture
Select vh.VoucharId,fCity.CityName as FromCity, tCity.CityName as ToCity, InDate
from VoucharHotel vh
inner join City fCity on vh.City = fCity.CityId inner join City tCity on
vh.City = tCity.CityId
where vh.InDate between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM' AND City in (1,2)
CREATE TABLE VoucharHotel (
ID int IDENTITY(1,1) PRIMARY KEY,
VoucharId Int ,
CityId int,
HotelId int,
InDate Datetime,
OutDate Datetime
);
CREATE TABLE City (
CityId int IDENTITY(1,1) PRIMARY KEY,
CityName varchar(200),
);
insert into City Values('London')
insert into City Values('Manchester')
insert into City Values('Birmingham')
insert into City Values('Leeds')
CREATE TABLE HotelMaster (
HotelId int IDENTITY(1,1) PRIMARY KEY,
HotelName varchar(200),
);
insert into HotelMaster Values('London Hotel')
insert into HotelMaster Values('Manchester Hotel')
insert into HotelMaster Values('Birmingham Hotel')
insert into HotelMaster Values('Leeds Hotel')
Insert into VoucharHotel Values(22,1,1,'11/15/2018', '11/16/2018')
Insert into VoucharHotel Values(22,2,2,'11/16/2018', '11/18/2018')
Insert into VoucharHotel Values(22,1,1,'11/18/2018', '11/20/2018')
Insert into VoucharHotel Values(23,2,2,'11/16/2018', '11/17/2018')
Insert into VoucharHotel Values(23,4,4,'11/17/2018', '11/20/2018')
Insert into VoucharHotel Values(23,2,2,'11/20/2018', '11/26/2018')
sql-server sql-server-2012 sql-server-2014 asked Nov 20 '18 at 23:49
JOhnsJOhns
529
add a comment |
-1
I have below query I want to show data fromCityId AND ToCityId . Suppose passenger travel fromCity London toCity Manchester. How do I write query like this, when I use where clause and in, its show me same values in fromcity and tocity
Expected results are in below Picture
Select vh.VoucharId,fCity.CityName as FromCity, tCity.CityName as ToCity, InDate
from VoucharHotel vh
inner join City fCity on vh.City = fCity.CityId inner join City tCity on
vh.City = tCity.CityId
where vh.InDate between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM' AND City in (1,2)
CREATE TABLE VoucharHotel (
ID int IDENTITY(1,1) PRIMARY KEY,
VoucharId Int ,
CityId int,
HotelId int,
InDate Datetime,
OutDate Datetime
);
CREATE TABLE City (
CityId int IDENTITY(1,1) PRIMARY KEY,
CityName varchar(200),
);
insert into City Values('London')
insert into City Values('Manchester')
insert into City Values('Birmingham')
insert into City Values('Leeds')
CREATE TABLE HotelMaster (
HotelId int IDENTITY(1,1) PRIMARY KEY,
HotelName varchar(200),
);
insert into HotelMaster Values('London Hotel')
insert into HotelMaster Values('Manchester Hotel')
insert into HotelMaster Values('Birmingham Hotel')
insert into HotelMaster Values('Leeds Hotel')
Insert into VoucharHotel Values(22,1,1,'11/15/2018', '11/16/2018')
Insert into VoucharHotel Values(22,2,2,'11/16/2018', '11/18/2018')
Insert into VoucharHotel Values(22,1,1,'11/18/2018', '11/20/2018')
Insert into VoucharHotel Values(23,2,2,'11/16/2018', '11/17/2018')
Insert into VoucharHotel Values(23,4,4,'11/17/2018', '11/20/2018')
Insert into VoucharHotel Values(23,2,2,'11/20/2018', '11/26/2018')
asked Nov 20 '18 at 23:49
JOhnsJOhns
529
add a comment |
-1
-1
-1
I have below query I want to show data fromCityId AND ToCityId . Suppose passenger travel fromCity London toCity Manchester. How do I write query like this, when I use where clause and in, its show me same values in fromcity and tocity
Expected results are in below Picture
Select vh.VoucharId,fCity.CityName as FromCity, tCity.CityName as ToCity, InDate
from VoucharHotel vh
inner join City fCity on vh.City = fCity.CityId inner join City tCity on
vh.City = tCity.CityId
where vh.InDate between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM' AND City in (1,2)
CREATE TABLE VoucharHotel (
ID int IDENTITY(1,1) PRIMARY KEY,
VoucharId Int ,
CityId int,
HotelId int,
InDate Datetime,
OutDate Datetime
);
CREATE TABLE City (
CityId int IDENTITY(1,1) PRIMARY KEY,
CityName varchar(200),
);
insert into City Values('London')
insert into City Values('Manchester')
insert into City Values('Birmingham')
insert into City Values('Leeds')
CREATE TABLE HotelMaster (
HotelId int IDENTITY(1,1) PRIMARY KEY,
HotelName varchar(200),
);
insert into HotelMaster Values('London Hotel')
insert into HotelMaster Values('Manchester Hotel')
insert into HotelMaster Values('Birmingham Hotel')
insert into HotelMaster Values('Leeds Hotel')
Insert into VoucharHotel Values(22,1,1,'11/15/2018', '11/16/2018')
Insert into VoucharHotel Values(22,2,2,'11/16/2018', '11/18/2018')
Insert into VoucharHotel Values(22,1,1,'11/18/2018', '11/20/2018')
Insert into VoucharHotel Values(23,2,2,'11/16/2018', '11/17/2018')
Insert into VoucharHotel Values(23,4,4,'11/17/2018', '11/20/2018')
Insert into VoucharHotel Values(23,2,2,'11/20/2018', '11/26/2018')
asked Nov 20 '18 at 23:49
JOhnsJOhns
529
I have below query I want to show data fromCityId AND ToCityId . Suppose passenger travel fromCity London toCity Manchester. How do I write query like this, when I use where clause and in, its show me same values in fromcity and tocity
Expected results are in below Picture
Select vh.VoucharId,fCity.CityName as FromCity, tCity.CityName as ToCity, InDate
from VoucharHotel vh
inner join City fCity on vh.City = fCity.CityId inner join City tCity on
vh.City = tCity.CityId
where vh.InDate between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM' AND City in (1,2)
CREATE TABLE VoucharHotel (
ID int IDENTITY(1,1) PRIMARY KEY,
VoucharId Int ,
CityId int,
HotelId int,
InDate Datetime,
OutDate Datetime
);
CREATE TABLE City (
CityId int IDENTITY(1,1) PRIMARY KEY,
CityName varchar(200),
);
insert into City Values('London')
insert into City Values('Manchester')
insert into City Values('Birmingham')
insert into City Values('Leeds')
CREATE TABLE HotelMaster (
HotelId int IDENTITY(1,1) PRIMARY KEY,
HotelName varchar(200),
);
insert into HotelMaster Values('London Hotel')
insert into HotelMaster Values('Manchester Hotel')
insert into HotelMaster Values('Birmingham Hotel')
insert into HotelMaster Values('Leeds Hotel')
Insert into VoucharHotel Values(22,1,1,'11/15/2018', '11/16/2018')
Insert into VoucharHotel Values(22,2,2,'11/16/2018', '11/18/2018')
Insert into VoucharHotel Values(22,1,1,'11/18/2018', '11/20/2018')
Insert into VoucharHotel Values(23,2,2,'11/16/2018', '11/17/2018')
Insert into VoucharHotel Values(23,4,4,'11/17/2018', '11/20/2018')
Insert into VoucharHotel Values(23,2,2,'11/20/2018', '11/26/2018')
asked Nov 20 '18 at 23:49
JOhnsJOhns
529
asked Nov 20 '18 at 23:49
JOhnsJOhns
529
edited Nov 21 '18 at 8:05
JOhns
asked Nov 20 '18 at 23:49
JOhnsJOhns
529
asked Nov 20 '18 at 23:49
JOhnsJOhns
529
asked Nov 20 '18 at 23:49
JOhnsJOhns
529
529
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
Would the below resolve your problem:
with cte
(VoucherID,FromCity,ToCity,InDate)
as
(
select
vh.VoucharId
, fCity.CityName as FromCity
, tCity.CityName as ToCity
, InDate
from VoucharHotel vh
inner join City fCity on vh.City = fCity.CityId
inner join City tCity on vh.City = tCity.CityId
where vh.InDate between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM'
)
select
*
from cte
where City in (1,2)
Let me know if you need changes made.
answered Nov 21 '18 at 6:05
BirelBirel
447113
thanks for your answer but its not working
– JOhns
Nov 21 '18 at 7:29
invalid City in where clause
– JOhns
Nov 21 '18 at 7:29
and when i run query without where clausse it show me wrong result
– JOhns
Nov 21 '18 at 7:30
Could you provide me with some sample data, I will then be able to alter the answer to fit your needs.
– Birel
Nov 21 '18 at 7:51
OK wait 4 minutes
– JOhns
Nov 21 '18 at 7:52
|
show 2 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53403330%2fhow-to-get-data-from-sql-server-tocityid-and-fromcityid-bases%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Would the below resolve your problem:
with cte
(VoucherID,FromCity,ToCity,InDate)
as
(
select
vh.VoucharId
, fCity.CityName as FromCity
, tCity.CityName as ToCity
, InDate
from VoucharHotel vh
inner join City fCity on vh.City = fCity.CityId
inner join City tCity on vh.City = tCity.CityId
where vh.InDate between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM'
)
select
*
from cte
where City in (1,2)
Let me know if you need changes made.
answered Nov 21 '18 at 6:05
BirelBirel
447113
thanks for your answer but its not working
– JOhns
Nov 21 '18 at 7:29
invalid City in where clause
– JOhns
Nov 21 '18 at 7:29
and when i run query without where clausse it show me wrong result
– JOhns
Nov 21 '18 at 7:30
Could you provide me with some sample data, I will then be able to alter the answer to fit your needs.
– Birel
Nov 21 '18 at 7:51
OK wait 4 minutes
– JOhns
Nov 21 '18 at 7:52
|
show 2 more comments
0
Would the below resolve your problem:
with cte
(VoucherID,FromCity,ToCity,InDate)
as
(
select
vh.VoucharId
, fCity.CityName as FromCity
, tCity.CityName as ToCity
, InDate
from VoucharHotel vh
inner join City fCity on vh.City = fCity.CityId
inner join City tCity on vh.City = tCity.CityId
where vh.InDate between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM'
)
select
*
from cte
where City in (1,2)
Let me know if you need changes made.
answered Nov 21 '18 at 6:05
BirelBirel
447113
thanks for your answer but its not working
– JOhns
Nov 21 '18 at 7:29
invalid City in where clause
– JOhns
Nov 21 '18 at 7:29
and when i run query without where clausse it show me wrong result
– JOhns
Nov 21 '18 at 7:30
Could you provide me with some sample data, I will then be able to alter the answer to fit your needs.
– Birel
Nov 21 '18 at 7:51
OK wait 4 minutes
– JOhns
Nov 21 '18 at 7:52
|
show 2 more comments
0
0
0
Would the below resolve your problem:
with cte
(VoucherID,FromCity,ToCity,InDate)
as
(
select
vh.VoucharId
, fCity.CityName as FromCity
, tCity.CityName as ToCity
, InDate
from VoucharHotel vh
inner join City fCity on vh.City = fCity.CityId
inner join City tCity on vh.City = tCity.CityId
where vh.InDate between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM'
)
select
*
from cte
where City in (1,2)
Let me know if you need changes made.
answered Nov 21 '18 at 6:05
BirelBirel
447113
Would the below resolve your problem:
with cte
(VoucherID,FromCity,ToCity,InDate)
as
(
select
vh.VoucharId
, fCity.CityName as FromCity
, tCity.CityName as ToCity
, InDate
from VoucharHotel vh
inner join City fCity on vh.City = fCity.CityId
inner join City tCity on vh.City = tCity.CityId
where vh.InDate between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM'
)
select
*
from cte
where City in (1,2)
Let me know if you need changes made.
answered Nov 21 '18 at 6:05
BirelBirel
447113
answered Nov 21 '18 at 6:05
BirelBirel
447113
answered Nov 21 '18 at 6:05
BirelBirel
447113
answered Nov 21 '18 at 6:05
BirelBirel
447113
447113
thanks for your answer but its not working
– JOhns
Nov 21 '18 at 7:29
invalid City in where clause
– JOhns
Nov 21 '18 at 7:29
and when i run query without where clausse it show me wrong result
– JOhns
Nov 21 '18 at 7:30
Could you provide me with some sample data, I will then be able to alter the answer to fit your needs.
– Birel
Nov 21 '18 at 7:51
OK wait 4 minutes
– JOhns
Nov 21 '18 at 7:52
|
show 2 more comments
thanks for your answer but its not working
– JOhns
Nov 21 '18 at 7:29
invalid City in where clause
– JOhns
Nov 21 '18 at 7:29
and when i run query without where clausse it show me wrong result
– JOhns
Nov 21 '18 at 7:30
Could you provide me with some sample data, I will then be able to alter the answer to fit your needs.
– Birel
Nov 21 '18 at 7:51
OK wait 4 minutes
– JOhns
Nov 21 '18 at 7:52
thanks for your answer but its not working
– JOhns
Nov 21 '18 at 7:29
thanks for your answer but its not working
– JOhns
Nov 21 '18 at 7:29
invalid City in where clause
– JOhns
Nov 21 '18 at 7:29
invalid City in where clause
– JOhns
Nov 21 '18 at 7:29
and when i run query without where clausse it show me wrong result
– JOhns
Nov 21 '18 at 7:30
and when i run query without where clausse it show me wrong result
– JOhns
Nov 21 '18 at 7:30
Could you provide me with some sample data, I will then be able to alter the answer to fit your needs.
– Birel
Nov 21 '18 at 7:51
Could you provide me with some sample data, I will then be able to alter the answer to fit your needs.
– Birel
Nov 21 '18 at 7:51
OK wait 4 minutes
– JOhns
Nov 21 '18 at 7:52
OK wait 4 minutes
– JOhns
Nov 21 '18 at 7:52
|
show 2 more comments
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53403330%2fhow-to-get-data-from-sql-server-tocityid-and-fromcityid-bases%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
