how to get first value of inner join distinct value from other table
0
i have below picture and query. the problem is my query show me wrong value, in multiple records in does not show hotelname correct, means i want first hotel from VoucherHotelTable but its show me Scond hotel due to Distinct i think please help me this how to handle
Select distinct v.VoucherId,u.Name,v.ArrivalFromCity,
CAST(v.ArrivalDate AS DATE) as ADDate, v.ArrivalFlightNo, hm.HotelName
from VoucherMaster v
inner join UserMaster u on v.AgentId = u.UserId
inner join VoucharHotel vh on v.VoucherId = vh.VoucharId
inner join HotelMaster hm on vh.HotelId = hm.HotelId
inner join AirportTerminal t on v.ArrivalTerminalId = t.AirprtTerminalId
where v.ArrivalDate between '11/15/2018 12:00:00 AM'
and '11/16/2018 12:00:00 AM' AND v.ArrivalSectorId = 3
sql-server sql-server-2012 sql-server-2014 edited Nov 15 '18 at 19:40
DanB
1,7501315
asked Nov 15 '18 at 19:29
JOhnsJOhns
5910
add a comment |
0
i have below picture and query. the problem is my query show me wrong value, in multiple records in does not show hotelname correct, means i want first hotel from VoucherHotelTable but its show me Scond hotel due to Distinct i think please help me this how to handle
Select distinct v.VoucherId,u.Name,v.ArrivalFromCity,
CAST(v.ArrivalDate AS DATE) as ADDate, v.ArrivalFlightNo, hm.HotelName
from VoucherMaster v
inner join UserMaster u on v.AgentId = u.UserId
inner join VoucharHotel vh on v.VoucherId = vh.VoucharId
inner join HotelMaster hm on vh.HotelId = hm.HotelId
inner join AirportTerminal t on v.ArrivalTerminalId = t.AirprtTerminalId
where v.ArrivalDate between '11/15/2018 12:00:00 AM'
and '11/16/2018 12:00:00 AM' AND v.ArrivalSectorId = 3
sql-server sql-server-2012 sql-server-2014 edited Nov 15 '18 at 19:40
DanB
1,7501315
asked Nov 15 '18 at 19:29
JOhnsJOhns
5910
I don't see anything that would match your condition forv.ArrivalDate between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM'. Read this blog on how to improve your question with emphasis on This sub post
– scsimon
Nov 15 '18 at 19:33
ok i wam editing my picture
– JOhns
Nov 15 '18 at 19:35
If you have many corresponding entries inVoucharHoteltable, you need a ranking function to get the first. docs.microsoft.com/en-us/sql/t-sql/functions/…
– DanB
Nov 15 '18 at 19:35
@scsimon In AD date there is 11/15/2018 i show wrong date
– JOhns
Nov 15 '18 at 19:37
DanB .. i am new in SQL server... :( can you help me this please
– JOhns
Nov 15 '18 at 19:38
add a comment |
0
0
0
i have below picture and query. the problem is my query show me wrong value, in multiple records in does not show hotelname correct, means i want first hotel from VoucherHotelTable but its show me Scond hotel due to Distinct i think please help me this how to handle
Select distinct v.VoucherId,u.Name,v.ArrivalFromCity,
CAST(v.ArrivalDate AS DATE) as ADDate, v.ArrivalFlightNo, hm.HotelName
from VoucherMaster v
inner join UserMaster u on v.AgentId = u.UserId
inner join VoucharHotel vh on v.VoucherId = vh.VoucharId
inner join HotelMaster hm on vh.HotelId = hm.HotelId
inner join AirportTerminal t on v.ArrivalTerminalId = t.AirprtTerminalId
where v.ArrivalDate between '11/15/2018 12:00:00 AM'
and '11/16/2018 12:00:00 AM' AND v.ArrivalSectorId = 3
sql-server sql-server-2012 sql-server-2014 edited Nov 15 '18 at 19:40
DanB
1,7501315
asked Nov 15 '18 at 19:29
JOhnsJOhns
5910
i have below picture and query. the problem is my query show me wrong value, in multiple records in does not show hotelname correct, means i want first hotel from VoucherHotelTable but its show me Scond hotel due to Distinct i think please help me this how to handle
Select distinct v.VoucherId,u.Name,v.ArrivalFromCity,
CAST(v.ArrivalDate AS DATE) as ADDate, v.ArrivalFlightNo, hm.HotelName
from VoucherMaster v
inner join UserMaster u on v.AgentId = u.UserId
inner join VoucharHotel vh on v.VoucherId = vh.VoucharId
inner join HotelMaster hm on vh.HotelId = hm.HotelId
inner join AirportTerminal t on v.ArrivalTerminalId = t.AirprtTerminalId
where v.ArrivalDate between '11/15/2018 12:00:00 AM'
and '11/16/2018 12:00:00 AM' AND v.ArrivalSectorId = 3
sql-server sql-server-2012 sql-server-2014 sql-server sql-server-2012 sql-server-2014 edited Nov 15 '18 at 19:40
DanB
1,7501315
asked Nov 15 '18 at 19:29
JOhnsJOhns
5910
edited Nov 15 '18 at 19:40
DanB
1,7501315
asked Nov 15 '18 at 19:29
JOhnsJOhns
5910
edited Nov 15 '18 at 19:40
DanB
1,7501315
edited Nov 15 '18 at 19:40
DanB
1,7501315
edited Nov 15 '18 at 19:40
DanB
1,7501315
1,7501315
asked Nov 15 '18 at 19:29
JOhnsJOhns
5910
asked Nov 15 '18 at 19:29
JOhnsJOhns
5910
asked Nov 15 '18 at 19:29
JOhnsJOhns
5910
5910
I don't see anything that would match your condition forv.ArrivalDate between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM'. Read this blog on how to improve your question with emphasis on This sub post
– scsimon
Nov 15 '18 at 19:33
ok i wam editing my picture
– JOhns
Nov 15 '18 at 19:35
If you have many corresponding entries inVoucharHoteltable, you need a ranking function to get the first. docs.microsoft.com/en-us/sql/t-sql/functions/…
– DanB
Nov 15 '18 at 19:35
@scsimon In AD date there is 11/15/2018 i show wrong date
– JOhns
Nov 15 '18 at 19:37
DanB .. i am new in SQL server... :( can you help me this please
– JOhns
Nov 15 '18 at 19:38
add a comment |
I don't see anything that would match your condition forv.ArrivalDate between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM'. Read this blog on how to improve your question with emphasis on This sub post
– scsimon
Nov 15 '18 at 19:33
ok i wam editing my picture
– JOhns
Nov 15 '18 at 19:35
If you have many corresponding entries inVoucharHoteltable, you need a ranking function to get the first. docs.microsoft.com/en-us/sql/t-sql/functions/…
– DanB
Nov 15 '18 at 19:35
@scsimon In AD date there is 11/15/2018 i show wrong date
– JOhns
Nov 15 '18 at 19:37
DanB .. i am new in SQL server... :( can you help me this please
– JOhns
Nov 15 '18 at 19:38
I don't see anything that would match your condition for
v.ArrivalDate between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM' . Read this blog on how to improve your question with emphasis on This sub post– scsimon
Nov 15 '18 at 19:33
I don't see anything that would match your condition for
v.ArrivalDate between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM' . Read this blog on how to improve your question with emphasis on This sub post– scsimon
Nov 15 '18 at 19:33
ok i wam editing my picture
– JOhns
Nov 15 '18 at 19:35
ok i wam editing my picture
– JOhns
Nov 15 '18 at 19:35
If you have many corresponding entries in
VoucharHotel table, you need a ranking function to get the first. docs.microsoft.com/en-us/sql/t-sql/functions/…– DanB
Nov 15 '18 at 19:35
If you have many corresponding entries in
VoucharHotel table, you need a ranking function to get the first. docs.microsoft.com/en-us/sql/t-sql/functions/…– DanB
Nov 15 '18 at 19:35
@scsimon In AD date there is 11/15/2018 i show wrong date
– JOhns
Nov 15 '18 at 19:37
@scsimon In AD date there is 11/15/2018 i show wrong date
– JOhns
Nov 15 '18 at 19:37
DanB .. i am new in SQL server... :( can you help me this please
– JOhns
Nov 15 '18 at 19:38
DanB .. i am new in SQL server... :( can you help me this please
– JOhns
Nov 15 '18 at 19:38
add a comment |
2 Answers
2
active
oldest
votes
1
This query is working through partition by.
Try this. rank() function should return a rank for hotel for each voucher.
;with VoucherHotelRnk as (
select voucherId, hotelId, date, rank() over(partition by voucherId order by date) rnk
from VoucharHotel
)
Select v.VoucherId,u.Name,v.ArrivalFromCity,
CAST(v.ArrivalDate AS DATE) as ADDate, v.ArrivalFlightNo, hm.HotelName
from VoucherMaster v
inner join UserMaster u on v.AgentId = u.UserId
inner join VoucherHotelRnk vh on v.VoucherId = vh.VoucherId and vh.rnk = 1
inner join HotelMaster hm on vh.HotelId = hm.HotelId
inner join AirportTerminal t on v.ArrivalTerminalId = t.AirprtTerminalId
where v.ArrivalDate between '11/15/2018 12:00:00 AM'
and '11/16/2018 12:00:00 AM' AND v.ArrivalSectorId = 3
edited Nov 21 '18 at 11:50
Community♦
11
answered Nov 15 '18 at 19:44
DanBDanB
1,7501315
Invalid column name 'voucherId'. Invalid column name 'hotelId'. Thanks for your answer highly appreciated but its show me this error when i execute this query
– JOhns
Nov 15 '18 at 19:50
I have updated my query. I have forgot to mention voucherhotel table within the cte.
– DanB
Nov 15 '18 at 19:54
same erro :( Invalid column name 'voucherId'.
– JOhns
Nov 15 '18 at 20:08
It's not clear. Your capture show voucharHotel (with a a), and a column voucherId (with a e). And your query show voucharId (with a a). I think you can fix mispelling error by yourself (not sure with is the good spelling in your DB).
– DanB
Nov 15 '18 at 20:11
Ok Thanks bro i will check
– JOhns
Nov 15 '18 at 20:14
|
show 2 more comments
-1
;with VoucherHotelRnk as (
select voucherId, hotelId, date, ROW_NUMBER() over(partition by voucherId order by date) rowcount
from VoucharHotel
)
Select v.VoucherId,u.Name,v.ArrivalFromCity,
CAST(v.ArrivalDate AS DATE) as ADDate, v.ArrivalFlightNo, hm.HotelName
from VoucherMaster v
inner join UserMaster u on v.AgentId = u.UserId
inner join VoucherHotelRnk vh on v.VoucherId = vh.VoucherId and vh.rowcount= 1
inner join HotelMaster hm on vh.HotelId = hm.HotelId
inner join AirportTerminal t on v.ArrivalTerminalId = t.AirprtTerminalId
where v.ArrivalDate between '11/15/2018 12:00:00 AM'
and '11/16/2018 12:00:00 AM' AND v.ArrivalSectorId = 3
answered Nov 21 '18 at 8:57
Tahir KhanTahir Khan
11
Hi and welcome to Stack Overflow. In order for this to be an appropriate answer, you must first explain your answer, so that OP can understand WHY this is the correct solution, or determine whether it is NOT the correct solution. You cannot just post a code block.
– cmprogram
Nov 21 '18 at 9:05
add a comment |
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%2f53326674%2fhow-to-get-first-value-of-inner-join-distinct-value-from-other-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
This query is working through partition by.
Try this. rank() function should return a rank for hotel for each voucher.
;with VoucherHotelRnk as (
select voucherId, hotelId, date, rank() over(partition by voucherId order by date) rnk
from VoucharHotel
)
Select v.VoucherId,u.Name,v.ArrivalFromCity,
CAST(v.ArrivalDate AS DATE) as ADDate, v.ArrivalFlightNo, hm.HotelName
from VoucherMaster v
inner join UserMaster u on v.AgentId = u.UserId
inner join VoucherHotelRnk vh on v.VoucherId = vh.VoucherId and vh.rnk = 1
inner join HotelMaster hm on vh.HotelId = hm.HotelId
inner join AirportTerminal t on v.ArrivalTerminalId = t.AirprtTerminalId
where v.ArrivalDate between '11/15/2018 12:00:00 AM'
and '11/16/2018 12:00:00 AM' AND v.ArrivalSectorId = 3
edited Nov 21 '18 at 11:50
Community♦
11
answered Nov 15 '18 at 19:44
DanBDanB
1,7501315
Invalid column name 'voucherId'. Invalid column name 'hotelId'. Thanks for your answer highly appreciated but its show me this error when i execute this query
– JOhns
Nov 15 '18 at 19:50
I have updated my query. I have forgot to mention voucherhotel table within the cte.
– DanB
Nov 15 '18 at 19:54
same erro :( Invalid column name 'voucherId'.
– JOhns
Nov 15 '18 at 20:08
It's not clear. Your capture show voucharHotel (with a a), and a column voucherId (with a e). And your query show voucharId (with a a). I think you can fix mispelling error by yourself (not sure with is the good spelling in your DB).
– DanB
Nov 15 '18 at 20:11
Ok Thanks bro i will check
– JOhns
Nov 15 '18 at 20:14
|
show 2 more comments
1
This query is working through partition by.
Try this. rank() function should return a rank for hotel for each voucher.
;with VoucherHotelRnk as (
select voucherId, hotelId, date, rank() over(partition by voucherId order by date) rnk
from VoucharHotel
)
Select v.VoucherId,u.Name,v.ArrivalFromCity,
CAST(v.ArrivalDate AS DATE) as ADDate, v.ArrivalFlightNo, hm.HotelName
from VoucherMaster v
inner join UserMaster u on v.AgentId = u.UserId
inner join VoucherHotelRnk vh on v.VoucherId = vh.VoucherId and vh.rnk = 1
inner join HotelMaster hm on vh.HotelId = hm.HotelId
inner join AirportTerminal t on v.ArrivalTerminalId = t.AirprtTerminalId
where v.ArrivalDate between '11/15/2018 12:00:00 AM'
and '11/16/2018 12:00:00 AM' AND v.ArrivalSectorId = 3
edited Nov 21 '18 at 11:50
Community♦
11
answered Nov 15 '18 at 19:44
DanBDanB
1,7501315
Invalid column name 'voucherId'. Invalid column name 'hotelId'. Thanks for your answer highly appreciated but its show me this error when i execute this query
– JOhns
Nov 15 '18 at 19:50
I have updated my query. I have forgot to mention voucherhotel table within the cte.
– DanB
Nov 15 '18 at 19:54
same erro :( Invalid column name 'voucherId'.
– JOhns
Nov 15 '18 at 20:08
It's not clear. Your capture show voucharHotel (with a a), and a column voucherId (with a e). And your query show voucharId (with a a). I think you can fix mispelling error by yourself (not sure with is the good spelling in your DB).
– DanB
Nov 15 '18 at 20:11
Ok Thanks bro i will check
– JOhns
Nov 15 '18 at 20:14
|
show 2 more comments
1
1
1
This query is working through partition by.
Try this. rank() function should return a rank for hotel for each voucher.
;with VoucherHotelRnk as (
select voucherId, hotelId, date, rank() over(partition by voucherId order by date) rnk
from VoucharHotel
)
Select v.VoucherId,u.Name,v.ArrivalFromCity,
CAST(v.ArrivalDate AS DATE) as ADDate, v.ArrivalFlightNo, hm.HotelName
from VoucherMaster v
inner join UserMaster u on v.AgentId = u.UserId
inner join VoucherHotelRnk vh on v.VoucherId = vh.VoucherId and vh.rnk = 1
inner join HotelMaster hm on vh.HotelId = hm.HotelId
inner join AirportTerminal t on v.ArrivalTerminalId = t.AirprtTerminalId
where v.ArrivalDate between '11/15/2018 12:00:00 AM'
and '11/16/2018 12:00:00 AM' AND v.ArrivalSectorId = 3
edited Nov 21 '18 at 11:50
Community♦
11
answered Nov 15 '18 at 19:44
DanBDanB
1,7501315
This query is working through partition by.
Try this. rank() function should return a rank for hotel for each voucher.
;with VoucherHotelRnk as (
select voucherId, hotelId, date, rank() over(partition by voucherId order by date) rnk
from VoucharHotel
)
Select v.VoucherId,u.Name,v.ArrivalFromCity,
CAST(v.ArrivalDate AS DATE) as ADDate, v.ArrivalFlightNo, hm.HotelName
from VoucherMaster v
inner join UserMaster u on v.AgentId = u.UserId
inner join VoucherHotelRnk vh on v.VoucherId = vh.VoucherId and vh.rnk = 1
inner join HotelMaster hm on vh.HotelId = hm.HotelId
inner join AirportTerminal t on v.ArrivalTerminalId = t.AirprtTerminalId
where v.ArrivalDate between '11/15/2018 12:00:00 AM'
and '11/16/2018 12:00:00 AM' AND v.ArrivalSectorId = 3
edited Nov 21 '18 at 11:50
Community♦
11
answered Nov 15 '18 at 19:44
DanBDanB
1,7501315
edited Nov 21 '18 at 11:50
Community♦
11
edited Nov 21 '18 at 11:50
Community♦
11
edited Nov 21 '18 at 11:50
Community♦
11
11
answered Nov 15 '18 at 19:44
DanBDanB
1,7501315
answered Nov 15 '18 at 19:44
DanBDanB
1,7501315
answered Nov 15 '18 at 19:44
DanBDanB
1,7501315
1,7501315
Invalid column name 'voucherId'. Invalid column name 'hotelId'. Thanks for your answer highly appreciated but its show me this error when i execute this query
– JOhns
Nov 15 '18 at 19:50
I have updated my query. I have forgot to mention voucherhotel table within the cte.
– DanB
Nov 15 '18 at 19:54
same erro :( Invalid column name 'voucherId'.
– JOhns
Nov 15 '18 at 20:08
It's not clear. Your capture show voucharHotel (with a a), and a column voucherId (with a e). And your query show voucharId (with a a). I think you can fix mispelling error by yourself (not sure with is the good spelling in your DB).
– DanB
Nov 15 '18 at 20:11
Ok Thanks bro i will check
– JOhns
Nov 15 '18 at 20:14
|
show 2 more comments
Invalid column name 'voucherId'. Invalid column name 'hotelId'. Thanks for your answer highly appreciated but its show me this error when i execute this query
– JOhns
Nov 15 '18 at 19:50
I have updated my query. I have forgot to mention voucherhotel table within the cte.
– DanB
Nov 15 '18 at 19:54
same erro :( Invalid column name 'voucherId'.
– JOhns
Nov 15 '18 at 20:08
It's not clear. Your capture show voucharHotel (with a a), and a column voucherId (with a e). And your query show voucharId (with a a). I think you can fix mispelling error by yourself (not sure with is the good spelling in your DB).
– DanB
Nov 15 '18 at 20:11
Ok Thanks bro i will check
– JOhns
Nov 15 '18 at 20:14
Invalid column name 'voucherId'. Invalid column name 'hotelId'. Thanks for your answer highly appreciated but its show me this error when i execute this query
– JOhns
Nov 15 '18 at 19:50
Invalid column name 'voucherId'. Invalid column name 'hotelId'. Thanks for your answer highly appreciated but its show me this error when i execute this query
– JOhns
Nov 15 '18 at 19:50
I have updated my query. I have forgot to mention voucherhotel table within the cte.
– DanB
Nov 15 '18 at 19:54
I have updated my query. I have forgot to mention voucherhotel table within the cte.
– DanB
Nov 15 '18 at 19:54
same erro :( Invalid column name 'voucherId'.
– JOhns
Nov 15 '18 at 20:08
same erro :( Invalid column name 'voucherId'.
– JOhns
Nov 15 '18 at 20:08
It's not clear. Your capture show voucharHotel (with a a), and a column voucherId (with a e). And your query show voucharId (with a a). I think you can fix mispelling error by yourself (not sure with is the good spelling in your DB).
– DanB
Nov 15 '18 at 20:11
It's not clear. Your capture show voucharHotel (with a a), and a column voucherId (with a e). And your query show voucharId (with a a). I think you can fix mispelling error by yourself (not sure with is the good spelling in your DB).
– DanB
Nov 15 '18 at 20:11
Ok Thanks bro i will check
– JOhns
Nov 15 '18 at 20:14
Ok Thanks bro i will check
– JOhns
Nov 15 '18 at 20:14
|
show 2 more comments
-1
;with VoucherHotelRnk as (
select voucherId, hotelId, date, ROW_NUMBER() over(partition by voucherId order by date) rowcount
from VoucharHotel
)
Select v.VoucherId,u.Name,v.ArrivalFromCity,
CAST(v.ArrivalDate AS DATE) as ADDate, v.ArrivalFlightNo, hm.HotelName
from VoucherMaster v
inner join UserMaster u on v.AgentId = u.UserId
inner join VoucherHotelRnk vh on v.VoucherId = vh.VoucherId and vh.rowcount= 1
inner join HotelMaster hm on vh.HotelId = hm.HotelId
inner join AirportTerminal t on v.ArrivalTerminalId = t.AirprtTerminalId
where v.ArrivalDate between '11/15/2018 12:00:00 AM'
and '11/16/2018 12:00:00 AM' AND v.ArrivalSectorId = 3
answered Nov 21 '18 at 8:57
Tahir KhanTahir Khan
11
Hi and welcome to Stack Overflow. In order for this to be an appropriate answer, you must first explain your answer, so that OP can understand WHY this is the correct solution, or determine whether it is NOT the correct solution. You cannot just post a code block.
– cmprogram
Nov 21 '18 at 9:05
add a comment |
-1
;with VoucherHotelRnk as (
select voucherId, hotelId, date, ROW_NUMBER() over(partition by voucherId order by date) rowcount
from VoucharHotel
)
Select v.VoucherId,u.Name,v.ArrivalFromCity,
CAST(v.ArrivalDate AS DATE) as ADDate, v.ArrivalFlightNo, hm.HotelName
from VoucherMaster v
inner join UserMaster u on v.AgentId = u.UserId
inner join VoucherHotelRnk vh on v.VoucherId = vh.VoucherId and vh.rowcount= 1
inner join HotelMaster hm on vh.HotelId = hm.HotelId
inner join AirportTerminal t on v.ArrivalTerminalId = t.AirprtTerminalId
where v.ArrivalDate between '11/15/2018 12:00:00 AM'
and '11/16/2018 12:00:00 AM' AND v.ArrivalSectorId = 3
answered Nov 21 '18 at 8:57
Tahir KhanTahir Khan
11
Hi and welcome to Stack Overflow. In order for this to be an appropriate answer, you must first explain your answer, so that OP can understand WHY this is the correct solution, or determine whether it is NOT the correct solution. You cannot just post a code block.
– cmprogram
Nov 21 '18 at 9:05
add a comment |
-1
-1
-1
;with VoucherHotelRnk as (
select voucherId, hotelId, date, ROW_NUMBER() over(partition by voucherId order by date) rowcount
from VoucharHotel
)
Select v.VoucherId,u.Name,v.ArrivalFromCity,
CAST(v.ArrivalDate AS DATE) as ADDate, v.ArrivalFlightNo, hm.HotelName
from VoucherMaster v
inner join UserMaster u on v.AgentId = u.UserId
inner join VoucherHotelRnk vh on v.VoucherId = vh.VoucherId and vh.rowcount= 1
inner join HotelMaster hm on vh.HotelId = hm.HotelId
inner join AirportTerminal t on v.ArrivalTerminalId = t.AirprtTerminalId
where v.ArrivalDate between '11/15/2018 12:00:00 AM'
and '11/16/2018 12:00:00 AM' AND v.ArrivalSectorId = 3
answered Nov 21 '18 at 8:57
Tahir KhanTahir Khan
11
;with VoucherHotelRnk as (
select voucherId, hotelId, date, ROW_NUMBER() over(partition by voucherId order by date) rowcount
from VoucharHotel
)
Select v.VoucherId,u.Name,v.ArrivalFromCity,
CAST(v.ArrivalDate AS DATE) as ADDate, v.ArrivalFlightNo, hm.HotelName
from VoucherMaster v
inner join UserMaster u on v.AgentId = u.UserId
inner join VoucherHotelRnk vh on v.VoucherId = vh.VoucherId and vh.rowcount= 1
inner join HotelMaster hm on vh.HotelId = hm.HotelId
inner join AirportTerminal t on v.ArrivalTerminalId = t.AirprtTerminalId
where v.ArrivalDate between '11/15/2018 12:00:00 AM'
and '11/16/2018 12:00:00 AM' AND v.ArrivalSectorId = 3
answered Nov 21 '18 at 8:57
Tahir KhanTahir Khan
11
answered Nov 21 '18 at 8:57
Tahir KhanTahir Khan
11
answered Nov 21 '18 at 8:57
Tahir KhanTahir Khan
11
answered Nov 21 '18 at 8:57
Tahir KhanTahir Khan
11
11
Hi and welcome to Stack Overflow. In order for this to be an appropriate answer, you must first explain your answer, so that OP can understand WHY this is the correct solution, or determine whether it is NOT the correct solution. You cannot just post a code block.
– cmprogram
Nov 21 '18 at 9:05
add a comment |
Hi and welcome to Stack Overflow. In order for this to be an appropriate answer, you must first explain your answer, so that OP can understand WHY this is the correct solution, or determine whether it is NOT the correct solution. You cannot just post a code block.
– cmprogram
Nov 21 '18 at 9:05
Hi and welcome to Stack Overflow. In order for this to be an appropriate answer, you must first explain your answer, so that OP can understand WHY this is the correct solution, or determine whether it is NOT the correct solution. You cannot just post a code block.
– cmprogram
Nov 21 '18 at 9:05
Hi and welcome to Stack Overflow. In order for this to be an appropriate answer, you must first explain your answer, so that OP can understand WHY this is the correct solution, or determine whether it is NOT the correct solution. You cannot just post a code block.
– cmprogram
Nov 21 '18 at 9:05
add a comment |
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%2f53326674%2fhow-to-get-first-value-of-inner-join-distinct-value-from-other-table%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
I don't see anything that would match your condition for
v.ArrivalDate between '11/15/2018 12:00:00 AM' and '11/16/2018 12:00:00 AM'. Read this blog on how to improve your question with emphasis on This sub post– scsimon
Nov 15 '18 at 19:33
ok i wam editing my picture
– JOhns
Nov 15 '18 at 19:35
If you have many corresponding entries in
VoucharHoteltable, you need a ranking function to get the first. docs.microsoft.com/en-us/sql/t-sql/functions/…– DanB
Nov 15 '18 at 19:35
@scsimon In AD date there is 11/15/2018 i show wrong date
– JOhns
Nov 15 '18 at 19:37
DanB .. i am new in SQL server... :( can you help me this please
– JOhns
Nov 15 '18 at 19:38