Mixing
MS SQL Join Two Tables with 'IN' Faces Problem
1
I have two tables for my system.
Table 1: Book
Table 2: BookStatus
Now, I want to find out the latest status of books that had been lent from 2018/11/05 to 201/11/10 and how many times they have been lent during this period.
The result I expect is like:
My SQL script is:
SELECT BK.*,
BS.status, BS.statusDateTime,
(SELECT count(*) FROM BookStatus WHERE status='lent' AND bookId=BK.bookId AND statusDateTime>='2018/11/05 00:00:00' AND statusDatetime<='2018/11/10 23:59:59' ) CountLent
FROM Book BK LEFT JOIN BookStatus BS ON BK.bookId = BS.bookId
WHERE BS.statusId = (SELECT max(statusId) FROM BookStatus WHERE bookId= BK.bookId)
AND BK.bookId IN (SELECT bookId FROM BookStatus WHERE status='lent' AND statusDateTime>='2018/11/05 00:00:00' AND statusDatetime<='2018/11/10 23:59:59')
After I execute the sql query, it just keeps running and doesn't stop.
Is there any problem with my SQL query?
How to revise my SQL script to have the result I expect?
sql
sql-server join asked Nov 22 '18 at 8:07
edyuchengedyucheng
135
add a comment |
1
I have two tables for my system.
Table 1: Book
Table 2: BookStatus
Now, I want to find out the latest status of books that had been lent from 2018/11/05 to 201/11/10 and how many times they have been lent during this period.
The result I expect is like:
My SQL script is:
SELECT BK.*,
BS.status, BS.statusDateTime,
(SELECT count(*) FROM BookStatus WHERE status='lent' AND bookId=BK.bookId AND statusDateTime>='2018/11/05 00:00:00' AND statusDatetime<='2018/11/10 23:59:59' ) CountLent
FROM Book BK LEFT JOIN BookStatus BS ON BK.bookId = BS.bookId
WHERE BS.statusId = (SELECT max(statusId) FROM BookStatus WHERE bookId= BK.bookId)
AND BK.bookId IN (SELECT bookId FROM BookStatus WHERE status='lent' AND statusDateTime>='2018/11/05 00:00:00' AND statusDatetime<='2018/11/10 23:59:59')
After I execute the sql query, it just keeps running and doesn't stop.
Is there any problem with my SQL query?
How to revise my SQL script to have the result I expect?
sql
sql-server join asked Nov 22 '18 at 8:07
edyuchengedyucheng
135
2
Post tables and results as text, not as images. And learn to use a proper JOIN
– Joakim Danielson
Nov 22 '18 at 8:11
I tried FROM Book BK LEFT JOIN BookStatus BS ON BK.bookId=BS.bookId, but it stil keeps running and doesn't stop.
– edyucheng
Nov 22 '18 at 8:32
That is not my point, using JOIN is the preferred and best way to combine (join) two tables.
– Joakim Danielson
Nov 22 '18 at 8:41
Joakim Danielson Thanks for your suggestion.
– edyucheng
Nov 22 '18 at 9:26
add a comment |
1
1
1
I have two tables for my system.
Table 1: Book
Table 2: BookStatus
Now, I want to find out the latest status of books that had been lent from 2018/11/05 to 201/11/10 and how many times they have been lent during this period.
The result I expect is like:
My SQL script is:
SELECT BK.*,
BS.status, BS.statusDateTime,
(SELECT count(*) FROM BookStatus WHERE status='lent' AND bookId=BK.bookId AND statusDateTime>='2018/11/05 00:00:00' AND statusDatetime<='2018/11/10 23:59:59' ) CountLent
FROM Book BK LEFT JOIN BookStatus BS ON BK.bookId = BS.bookId
WHERE BS.statusId = (SELECT max(statusId) FROM BookStatus WHERE bookId= BK.bookId)
AND BK.bookId IN (SELECT bookId FROM BookStatus WHERE status='lent' AND statusDateTime>='2018/11/05 00:00:00' AND statusDatetime<='2018/11/10 23:59:59')
After I execute the sql query, it just keeps running and doesn't stop.
Is there any problem with my SQL query?
How to revise my SQL script to have the result I expect?
sql
sql-server join asked Nov 22 '18 at 8:07
edyuchengedyucheng
135
I have two tables for my system.
Table 1: Book
Table 2: BookStatus
Now, I want to find out the latest status of books that had been lent from 2018/11/05 to 201/11/10 and how many times they have been lent during this period.
The result I expect is like:
My SQL script is:
SELECT BK.*,
BS.status, BS.statusDateTime,
(SELECT count(*) FROM BookStatus WHERE status='lent' AND bookId=BK.bookId AND statusDateTime>='2018/11/05 00:00:00' AND statusDatetime<='2018/11/10 23:59:59' ) CountLent
FROM Book BK LEFT JOIN BookStatus BS ON BK.bookId = BS.bookId
WHERE BS.statusId = (SELECT max(statusId) FROM BookStatus WHERE bookId= BK.bookId)
AND BK.bookId IN (SELECT bookId FROM BookStatus WHERE status='lent' AND statusDateTime>='2018/11/05 00:00:00' AND statusDatetime<='2018/11/10 23:59:59')
After I execute the sql query, it just keeps running and doesn't stop.
Is there any problem with my SQL query?
How to revise my SQL script to have the result I expect?
sql
sql-server join sql
sql-server join asked Nov 22 '18 at 8:07
edyuchengedyucheng
135
asked Nov 22 '18 at 8:07
edyuchengedyucheng
135
edited Nov 23 '18 at 3:44
edyucheng
asked Nov 22 '18 at 8:07
edyuchengedyucheng
135
asked Nov 22 '18 at 8:07
edyuchengedyucheng
135
asked Nov 22 '18 at 8:07
edyuchengedyucheng
135
135
2
Post tables and results as text, not as images. And learn to use a proper JOIN
– Joakim Danielson
Nov 22 '18 at 8:11
I tried FROM Book BK LEFT JOIN BookStatus BS ON BK.bookId=BS.bookId, but it stil keeps running and doesn't stop.
– edyucheng
Nov 22 '18 at 8:32
That is not my point, using JOIN is the preferred and best way to combine (join) two tables.
– Joakim Danielson
Nov 22 '18 at 8:41
Joakim Danielson Thanks for your suggestion.
– edyucheng
Nov 22 '18 at 9:26
add a comment |
2
Post tables and results as text, not as images. And learn to use a proper JOIN
– Joakim Danielson
Nov 22 '18 at 8:11
I tried FROM Book BK LEFT JOIN BookStatus BS ON BK.bookId=BS.bookId, but it stil keeps running and doesn't stop.
– edyucheng
Nov 22 '18 at 8:32
That is not my point, using JOIN is the preferred and best way to combine (join) two tables.
– Joakim Danielson
Nov 22 '18 at 8:41
Joakim Danielson Thanks for your suggestion.
– edyucheng
Nov 22 '18 at 9:26
2
2
Post tables and results as text, not as images. And learn to use a proper JOIN
– Joakim Danielson
Nov 22 '18 at 8:11
Post tables and results as text, not as images. And learn to use a proper JOIN
– Joakim Danielson
Nov 22 '18 at 8:11
I tried FROM Book BK LEFT JOIN BookStatus BS ON BK.bookId=BS.bookId, but it stil keeps running and doesn't stop.
– edyucheng
Nov 22 '18 at 8:32
I tried FROM Book BK LEFT JOIN BookStatus BS ON BK.bookId=BS.bookId, but it stil keeps running and doesn't stop.
– edyucheng
Nov 22 '18 at 8:32
That is not my point, using JOIN is the preferred and best way to combine (join) two tables.
– Joakim Danielson
Nov 22 '18 at 8:41
That is not my point, using JOIN is the preferred and best way to combine (join) two tables.
– Joakim Danielson
Nov 22 '18 at 8:41
Joakim Danielson Thanks for your suggestion.
– edyucheng
Nov 22 '18 at 9:26
Joakim Danielson Thanks for your suggestion.
– edyucheng
Nov 22 '18 at 9:26
add a comment |
4 Answers
4
active
oldest
votes
0
Find out all the books that were lent during the period and the count using a sub query. Use CROSS APPLY to fetch the latest status:
WITH activity AS (
SELECT bookId, COUNT(*) AS CountLent
FROM BookStatus
WHERE statusDateTime >= '2018-11-05' AND statusDateTime < DATEADD(DAY, 1, '2018-11-10')
AND status = 'lent'
GROUP BY bookId
)
SELECT Book.*, activity.CountLent, lastStatus.*
FROM Book
INNER JOIN activity ON Book.bookId = activity.bookId
CROSS APPLY (SELECT TOP 1 status, statusDateTime FROM BookStatus WHERE bookId = Book.bookId ORDER BY statusDateTime DESC) AS lastStatus
answered Nov 23 '18 at 6:32
Salman ASalman A
182k66340434
Your code looks much simple and understandable withCROSS APPLYto solve my problem . Thanks a lot and cheers!!
– edyucheng
Nov 29 '18 at 7:17
add a comment |
2
Please try your query like below-
select b.bookid ,b.bookName , b.Author
,bs.status ,max(bs.statusDatetime), count(bs.status) Countlent
from Book b
join BookStatus bs on b.bookid= bs.bookid
where bs.statusDateTime >='2018/11/05 00:00:00' AND bs.statusDatetime<='2018/11/10 23:59:59'
and bs.status = 'lent'
group by b.bookid ,b.bookName , b.Author ,bs.status
Hope this will help you.
answered Nov 22 '18 at 8:25
Bharati MathapatiBharati Mathapati
543
What if the latest status was "available" inside that time period?
– Salman A
Nov 22 '18 at 9:50
add a comment |
0
You can try below
select b.bookid ,b.bookName , b.Author ,bs.status ,
max(bs.statusDatetime) as statusDatetime, count(bs.status) Countlent
from Book b
join BookStatus bs on b.bookid= bs.bookid
where bs.statusDateTime >='2018/11/05 00:00:00' AND bs.statusDatetime<='2018/11/10 23:59:59' and bs.status = 'lent'
group by b.bookid ,b.bookName , b.Author ,bs.status
answered Nov 22 '18 at 8:33
fa06fa06
15k2917
add a comment |
0
I tried run your code, and it was ok, but result was not true! You can try this:
SELECT bd.bookID, bk.bookName, bk.author,
statusID, status, statusDateTime ,bc.countLent
FROM ( SELECT bookID, statusDate = MAX(statusDateTime)
FROM bookStatus
GROUP BY bookID
) AS bd
LEFT JOIN ( SELECT bookID, COUNT(*) AS countLent
FROM bookStatus
WHERE [status] = 'lent'
GROUP BY bookID
) AS bc ON bc.bookID = bd.bookID
LEFT JOIN dbo.bookStatus ON bookStatus.bookID = bc.bookID
AND bd.statusDate = dbo.bookStatus.statusDateTime
LEFT JOIN Book AS bk ON bk.bookID = bookStatus.bookID
The result is good but I know my code is not the best way :D Hope this's help
answered Nov 26 '18 at 2:58
Tuấn ĐinhTuấn Đinh
111
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%2f53426366%2fms-sql-join-two-tables-with-in-faces-problem%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Find out all the books that were lent during the period and the count using a sub query. Use CROSS APPLY to fetch the latest status:
WITH activity AS (
SELECT bookId, COUNT(*) AS CountLent
FROM BookStatus
WHERE statusDateTime >= '2018-11-05' AND statusDateTime < DATEADD(DAY, 1, '2018-11-10')
AND status = 'lent'
GROUP BY bookId
)
SELECT Book.*, activity.CountLent, lastStatus.*
FROM Book
INNER JOIN activity ON Book.bookId = activity.bookId
CROSS APPLY (SELECT TOP 1 status, statusDateTime FROM BookStatus WHERE bookId = Book.bookId ORDER BY statusDateTime DESC) AS lastStatus
answered Nov 23 '18 at 6:32
Salman ASalman A
182k66340434
Your code looks much simple and understandable withCROSS APPLYto solve my problem . Thanks a lot and cheers!!
– edyucheng
Nov 29 '18 at 7:17
add a comment |
0
Find out all the books that were lent during the period and the count using a sub query. Use CROSS APPLY to fetch the latest status:
WITH activity AS (
SELECT bookId, COUNT(*) AS CountLent
FROM BookStatus
WHERE statusDateTime >= '2018-11-05' AND statusDateTime < DATEADD(DAY, 1, '2018-11-10')
AND status = 'lent'
GROUP BY bookId
)
SELECT Book.*, activity.CountLent, lastStatus.*
FROM Book
INNER JOIN activity ON Book.bookId = activity.bookId
CROSS APPLY (SELECT TOP 1 status, statusDateTime FROM BookStatus WHERE bookId = Book.bookId ORDER BY statusDateTime DESC) AS lastStatus
answered Nov 23 '18 at 6:32
Salman ASalman A
182k66340434
Your code looks much simple and understandable withCROSS APPLYto solve my problem . Thanks a lot and cheers!!
– edyucheng
Nov 29 '18 at 7:17
add a comment |
0
0
0
Find out all the books that were lent during the period and the count using a sub query. Use CROSS APPLY to fetch the latest status:
WITH activity AS (
SELECT bookId, COUNT(*) AS CountLent
FROM BookStatus
WHERE statusDateTime >= '2018-11-05' AND statusDateTime < DATEADD(DAY, 1, '2018-11-10')
AND status = 'lent'
GROUP BY bookId
)
SELECT Book.*, activity.CountLent, lastStatus.*
FROM Book
INNER JOIN activity ON Book.bookId = activity.bookId
CROSS APPLY (SELECT TOP 1 status, statusDateTime FROM BookStatus WHERE bookId = Book.bookId ORDER BY statusDateTime DESC) AS lastStatus
answered Nov 23 '18 at 6:32
Salman ASalman A
182k66340434
Find out all the books that were lent during the period and the count using a sub query. Use CROSS APPLY to fetch the latest status:
WITH activity AS (
SELECT bookId, COUNT(*) AS CountLent
FROM BookStatus
WHERE statusDateTime >= '2018-11-05' AND statusDateTime < DATEADD(DAY, 1, '2018-11-10')
AND status = 'lent'
GROUP BY bookId
)
SELECT Book.*, activity.CountLent, lastStatus.*
FROM Book
INNER JOIN activity ON Book.bookId = activity.bookId
CROSS APPLY (SELECT TOP 1 status, statusDateTime FROM BookStatus WHERE bookId = Book.bookId ORDER BY statusDateTime DESC) AS lastStatus
answered Nov 23 '18 at 6:32
Salman ASalman A
182k66340434
answered Nov 23 '18 at 6:32
Salman ASalman A
182k66340434
answered Nov 23 '18 at 6:32
Salman ASalman A
182k66340434
answered Nov 23 '18 at 6:32
Salman ASalman A
182k66340434
182k66340434
Your code looks much simple and understandable withCROSS APPLYto solve my problem . Thanks a lot and cheers!!
– edyucheng
Nov 29 '18 at 7:17
add a comment |
Your code looks much simple and understandable withCROSS APPLYto solve my problem . Thanks a lot and cheers!!
– edyucheng
Nov 29 '18 at 7:17
Your code looks much simple and understandable with
CROSS APPLY to solve my problem . Thanks a lot and cheers!!– edyucheng
Nov 29 '18 at 7:17
Your code looks much simple and understandable with
CROSS APPLY to solve my problem . Thanks a lot and cheers!!– edyucheng
Nov 29 '18 at 7:17
add a comment |
2
Please try your query like below-
select b.bookid ,b.bookName , b.Author
,bs.status ,max(bs.statusDatetime), count(bs.status) Countlent
from Book b
join BookStatus bs on b.bookid= bs.bookid
where bs.statusDateTime >='2018/11/05 00:00:00' AND bs.statusDatetime<='2018/11/10 23:59:59'
and bs.status = 'lent'
group by b.bookid ,b.bookName , b.Author ,bs.status
Hope this will help you.
answered Nov 22 '18 at 8:25
Bharati MathapatiBharati Mathapati
543
What if the latest status was "available" inside that time period?
– Salman A
Nov 22 '18 at 9:50
add a comment |
2
Please try your query like below-
select b.bookid ,b.bookName , b.Author
,bs.status ,max(bs.statusDatetime), count(bs.status) Countlent
from Book b
join BookStatus bs on b.bookid= bs.bookid
where bs.statusDateTime >='2018/11/05 00:00:00' AND bs.statusDatetime<='2018/11/10 23:59:59'
and bs.status = 'lent'
group by b.bookid ,b.bookName , b.Author ,bs.status
Hope this will help you.
answered Nov 22 '18 at 8:25
Bharati MathapatiBharati Mathapati
543
What if the latest status was "available" inside that time period?
– Salman A
Nov 22 '18 at 9:50
add a comment |
2
2
2
Please try your query like below-
select b.bookid ,b.bookName , b.Author
,bs.status ,max(bs.statusDatetime), count(bs.status) Countlent
from Book b
join BookStatus bs on b.bookid= bs.bookid
where bs.statusDateTime >='2018/11/05 00:00:00' AND bs.statusDatetime<='2018/11/10 23:59:59'
and bs.status = 'lent'
group by b.bookid ,b.bookName , b.Author ,bs.status
Hope this will help you.
answered Nov 22 '18 at 8:25
Bharati MathapatiBharati Mathapati
543
Please try your query like below-
select b.bookid ,b.bookName , b.Author
,bs.status ,max(bs.statusDatetime), count(bs.status) Countlent
from Book b
join BookStatus bs on b.bookid= bs.bookid
where bs.statusDateTime >='2018/11/05 00:00:00' AND bs.statusDatetime<='2018/11/10 23:59:59'
and bs.status = 'lent'
group by b.bookid ,b.bookName , b.Author ,bs.status
Hope this will help you.
answered Nov 22 '18 at 8:25
Bharati MathapatiBharati Mathapati
543
edited Nov 22 '18 at 9:09
answered Nov 22 '18 at 8:25
Bharati MathapatiBharati Mathapati
543
answered Nov 22 '18 at 8:25
Bharati MathapatiBharati Mathapati
543
answered Nov 22 '18 at 8:25
Bharati MathapatiBharati Mathapati
543
543
What if the latest status was "available" inside that time period?
– Salman A
Nov 22 '18 at 9:50
add a comment |
What if the latest status was "available" inside that time period?
– Salman A
Nov 22 '18 at 9:50
What if the latest status was "available" inside that time period?
– Salman A
Nov 22 '18 at 9:50
What if the latest status was "available" inside that time period?
– Salman A
Nov 22 '18 at 9:50
add a comment |
0
You can try below
select b.bookid ,b.bookName , b.Author ,bs.status ,
max(bs.statusDatetime) as statusDatetime, count(bs.status) Countlent
from Book b
join BookStatus bs on b.bookid= bs.bookid
where bs.statusDateTime >='2018/11/05 00:00:00' AND bs.statusDatetime<='2018/11/10 23:59:59' and bs.status = 'lent'
group by b.bookid ,b.bookName , b.Author ,bs.status
answered Nov 22 '18 at 8:33
fa06fa06
15k2917
add a comment |
0
You can try below
select b.bookid ,b.bookName , b.Author ,bs.status ,
max(bs.statusDatetime) as statusDatetime, count(bs.status) Countlent
from Book b
join BookStatus bs on b.bookid= bs.bookid
where bs.statusDateTime >='2018/11/05 00:00:00' AND bs.statusDatetime<='2018/11/10 23:59:59' and bs.status = 'lent'
group by b.bookid ,b.bookName , b.Author ,bs.status
answered Nov 22 '18 at 8:33
fa06fa06
15k2917
add a comment |
0
0
0
You can try below
select b.bookid ,b.bookName , b.Author ,bs.status ,
max(bs.statusDatetime) as statusDatetime, count(bs.status) Countlent
from Book b
join BookStatus bs on b.bookid= bs.bookid
where bs.statusDateTime >='2018/11/05 00:00:00' AND bs.statusDatetime<='2018/11/10 23:59:59' and bs.status = 'lent'
group by b.bookid ,b.bookName , b.Author ,bs.status
answered Nov 22 '18 at 8:33
fa06fa06
15k2917
You can try below
select b.bookid ,b.bookName , b.Author ,bs.status ,
max(bs.statusDatetime) as statusDatetime, count(bs.status) Countlent
from Book b
join BookStatus bs on b.bookid= bs.bookid
where bs.statusDateTime >='2018/11/05 00:00:00' AND bs.statusDatetime<='2018/11/10 23:59:59' and bs.status = 'lent'
group by b.bookid ,b.bookName , b.Author ,bs.status
answered Nov 22 '18 at 8:33
fa06fa06
15k2917
answered Nov 22 '18 at 8:33
fa06fa06
15k2917
answered Nov 22 '18 at 8:33
fa06fa06
15k2917
answered Nov 22 '18 at 8:33
fa06fa06
15k2917
15k2917
add a comment |
add a comment |
0
I tried run your code, and it was ok, but result was not true! You can try this:
SELECT bd.bookID, bk.bookName, bk.author,
statusID, status, statusDateTime ,bc.countLent
FROM ( SELECT bookID, statusDate = MAX(statusDateTime)
FROM bookStatus
GROUP BY bookID
) AS bd
LEFT JOIN ( SELECT bookID, COUNT(*) AS countLent
FROM bookStatus
WHERE [status] = 'lent'
GROUP BY bookID
) AS bc ON bc.bookID = bd.bookID
LEFT JOIN dbo.bookStatus ON bookStatus.bookID = bc.bookID
AND bd.statusDate = dbo.bookStatus.statusDateTime
LEFT JOIN Book AS bk ON bk.bookID = bookStatus.bookID
The result is good but I know my code is not the best way :D Hope this's help
answered Nov 26 '18 at 2:58
Tuấn ĐinhTuấn Đinh
111
add a comment |
0
I tried run your code, and it was ok, but result was not true! You can try this:
SELECT bd.bookID, bk.bookName, bk.author,
statusID, status, statusDateTime ,bc.countLent
FROM ( SELECT bookID, statusDate = MAX(statusDateTime)
FROM bookStatus
GROUP BY bookID
) AS bd
LEFT JOIN ( SELECT bookID, COUNT(*) AS countLent
FROM bookStatus
WHERE [status] = 'lent'
GROUP BY bookID
) AS bc ON bc.bookID = bd.bookID
LEFT JOIN dbo.bookStatus ON bookStatus.bookID = bc.bookID
AND bd.statusDate = dbo.bookStatus.statusDateTime
LEFT JOIN Book AS bk ON bk.bookID = bookStatus.bookID
The result is good but I know my code is not the best way :D Hope this's help
answered Nov 26 '18 at 2:58
Tuấn ĐinhTuấn Đinh
111
add a comment |
0
0
0
I tried run your code, and it was ok, but result was not true! You can try this:
SELECT bd.bookID, bk.bookName, bk.author,
statusID, status, statusDateTime ,bc.countLent
FROM ( SELECT bookID, statusDate = MAX(statusDateTime)
FROM bookStatus
GROUP BY bookID
) AS bd
LEFT JOIN ( SELECT bookID, COUNT(*) AS countLent
FROM bookStatus
WHERE [status] = 'lent'
GROUP BY bookID
) AS bc ON bc.bookID = bd.bookID
LEFT JOIN dbo.bookStatus ON bookStatus.bookID = bc.bookID
AND bd.statusDate = dbo.bookStatus.statusDateTime
LEFT JOIN Book AS bk ON bk.bookID = bookStatus.bookID
The result is good but I know my code is not the best way :D Hope this's help
answered Nov 26 '18 at 2:58
Tuấn ĐinhTuấn Đinh
111
I tried run your code, and it was ok, but result was not true! You can try this:
SELECT bd.bookID, bk.bookName, bk.author,
statusID, status, statusDateTime ,bc.countLent
FROM ( SELECT bookID, statusDate = MAX(statusDateTime)
FROM bookStatus
GROUP BY bookID
) AS bd
LEFT JOIN ( SELECT bookID, COUNT(*) AS countLent
FROM bookStatus
WHERE [status] = 'lent'
GROUP BY bookID
) AS bc ON bc.bookID = bd.bookID
LEFT JOIN dbo.bookStatus ON bookStatus.bookID = bc.bookID
AND bd.statusDate = dbo.bookStatus.statusDateTime
LEFT JOIN Book AS bk ON bk.bookID = bookStatus.bookID
The result is good but I know my code is not the best way :D Hope this's help
answered Nov 26 '18 at 2:58
Tuấn ĐinhTuấn Đinh
111
answered Nov 26 '18 at 2:58
Tuấn ĐinhTuấn Đinh
111
answered Nov 26 '18 at 2:58
Tuấn ĐinhTuấn Đinh
111
answered Nov 26 '18 at 2:58
Tuấn ĐinhTuấn Đinh
111
111
add a comment |
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%2f53426366%2fms-sql-join-two-tables-with-in-faces-problem%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
2
Post tables and results as text, not as images. And learn to use a proper JOIN
– Joakim Danielson
Nov 22 '18 at 8:11
I tried FROM Book BK LEFT JOIN BookStatus BS ON BK.bookId=BS.bookId, but it stil keeps running and doesn't stop.
– edyucheng
Nov 22 '18 at 8:32
That is not my point, using JOIN is the preferred and best way to combine (join) two tables.
– Joakim Danielson
Nov 22 '18 at 8:41
Joakim Danielson Thanks for your suggestion.
– edyucheng
Nov 22 '18 at 9:26