Right outer join with filters

Question

Right outer join with filters

natasha.reiki

Valued Member

Points: 59
More actions
September 12, 2014 at 10:19 pm

#310932

Hi everyone
Noticed you are able to help with a similar query and were hoping you could assist with mine, I need to find;
Card number and date of borrowers earliest loan for all borrowers who had a loan before the 03/Jan/2004 OR who borrowed a book published before 1920
So far my query looks like this but it is bringing back date out after 03/Jan
select cardno, min(l.dateout)
from loan l right outer join book b
on l.isbn = b.isbn
where b.yearpublished < '1920'
or l.dateout < '03/Jan/2004'
group by cardno
order by cardno;
Any help would be appreciated as I am new to this, Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Hi and welcome to the forum.

Could you post the DDL (create table script) and possibly some sample data, makes it easier to provide an accurate answer, for more information, have a quick look at this article How to post data/code on a forum to get the best help[/url]

😎

venoym SSCarpal Tunnel Points: 4161 More actions · Answer 2

natasha.reiki,

Based on your description "Published Before 1920" OR "a loan before 03/Jan/2004" your code should return correctly.

I recommend to not use RIGHT OUTER JOIN as it can be confusing. LEFT OUTER JOIN applies to the tables in the SELECT in order of appearance (top to bottom), RIGHT OUTER JOIN does a reverse application to the tables (bottom to top).

You'll need to supply some sample data and expected results before we can understand what is not working about your code. In this case, DDL is probably not going to help much as this is a basic query question and we're probably missing something in the overlap between the two requirements.

select b.cardno, min(l.dateout)

from

dbo.book b left outer join

dbo.loan l on l.isbn = b.isbn

where

b.yearpublished < '1920' or

l.dateout < '03/Jan/2004'

group by b.cardno

order by b.cardno;

natasha.reiki (9/12/2014)
Hi everyone
Noticed you are able to help with a similar query and were hoping you could assist with mine, I need to find;
Card number and date of borrowers earliest loan for all borrowers who had a loan before the 03/Jan/2004 OR who borrowed a book published before 1920
So far my query looks like this but it is bringing back date out after 03/Jan
select cardno, min(l.dateout)
from loan l right outer join book b
on l.isbn = b.isbn
where b.yearpublished < '1920'
or l.dateout < '03/Jan/2004'
group by cardno
order by cardno;
Any help would be appreciated as I am new to this, Thanks

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

Here is a quick solution, should help get you passed this hurdle. It demonstrates that you can use INNER JOIN and retain the full details of the set by using simple CASE conditionals. The code is somewhat verbose in order to be more self explanatory.

😎

USE tempdb;

GO

SET NOCOUNT ON;

/* Simplified schema ****************************

+---------------+ +---------+

| BOOK | | LOAN |

+---------------+ +---------+

| isbn |-|-----------0<| isbn |

| Title | | dateout |

| yearpublished | | cardno |

+---------------+ +---------+

*************************************************/

IF OBJECT_ID('dbo.BOOK') IS NOT NULL

BEGIN

BEGIN TRY

ALTER TABLE dbo.LOAN DROP CONSTRAINT FK_DBO_LOAN_DBO_BOOK_ISBN;

END TRY

BEGIN CATCH

PRINT 'OOPS';

END CATCH

DROP TABLE dbo.BOOK;

END

CREATE TABLE dbo.BOOK

(

isbn VARCHAR(20) NOT NULL CONSTRAINT PK_DBO_BOOK_ISBN

PRIMARY KEY CLUSTERED

,Title VARCHAR(50) NOT NULL

,yearpublished DATE NOT NULL

);

IF OBJECT_ID('dbo.LOAN') IS NOT NULL DROP TABLE dbo.LOAN;

CREATE TABLE dbo.LOAN

(

cardno VARCHAR(15) NOT NULL

,isbn VARCHAR(20) NOT NULL CONSTRAINT FK_DBO_LOAN_DBO_BOOK_ISBN

FOREIGN KEY REFERENCES dbo.BOOK ( isbn )

,dateout DATE NOT NULL

,CONSTRAINT PK_DBO_LOAN_CARDNO_ISBN_DATEOUT PRIMARY KEY CLUSTERED

(cardno,isbn,dateout)

);

INSERT INTO dbo.BOOK

(

isbn

,Title

,yearpublished

) VALUES

('123-456-78-001','The Book of Topic 001','1910-01-01')

,('123-456-78-002','The Book of Topic 002','1911-02-01')

,('123-456-78-003','The Book of Topic 003','1919-03-01')

,('123-456-78-004','The Book of Topic 004','1920-04-01')

,('123-456-78-005','The Book of Topic 005','1921-05-01')

,('123-456-78-006','The Book of Topic 006','2001-06-01')

,('123-456-78-007','The Book of Topic 007','2002-07-01')

,('123-456-78-008','The Book of Topic 008','2003-08-01')

,('123-456-78-009','The Book of Topic 009','2004-09-01')

,('123-456-78-010','The Book of Topic 010','2005-10-01');

INSERT INTO dbo.LOAN

(

cardno

,isbn

,dateout

) VALUES

('9440-18400','123-456-78-001','2012-01-15')

,('9440-18400','123-456-78-002','2012-02-15')

,('9440-18400','123-456-78-003','2012-03-15')

,('9440-18400','123-456-78-004','2012-04-15')

,('9440-18400','123-456-78-005','2012-05-15')

,('9440-18400','123-456-78-006','2012-06-15')

,('9440-18400','123-456-78-007','2012-07-15')

,('9440-18400','123-456-78-008','2012-08-15')

,('9440-18400','123-456-78-009','2012-09-15')

,('9440-18400','123-456-78-010','2012-10-15')

,('1193-18404','123-456-78-006','2002-12-31')

,('1193-18404','123-456-78-007','2003-12-31')

,('1193-18404','123-456-78-008','2004-12-31')

,('1193-18404','123-456-78-009','2005-12-31')

,('1193-18404','123-456-78-010','2006-12-31')

,('3939-18414','123-456-78-003','2012-07-01')

,('3939-18414','123-456-78-004','2013-06-01')

,('3939-18414','123-456-78-005','2014-05-01')

,('8258-18447','123-456-78-004','2012-07-01')

,('8258-18447','123-456-78-006','2013-06-01')

,('8258-18447','123-456-78-008','2014-05-01')

,('8258-18447','123-456-78-010','2014-09-01')

,('4977-18459','123-456-78-008','2003-01-01')

,('8050-18463','123-456-78-003','2003-01-05');

;WITH LOAN_BOOK_BASE AS

(

SELECT

L.cardno

,L.isbn

,L.dateout

,CASE

WHEN L.dateout < '2003-01-03' THEN 1

ELSE 0

END AS LDO_20030103

,B.yearpublished

,CASE

WHEN YEAR(B.yearpublished) < 1920 THEN 1

ELSE 0

END AS PUB_1920

,B.Title

FROM LOAN L

INNER JOIN BOOK B

ON L.isbn = B.isbn

)

,FILTERED_SET AS

(

SELECT

LBB.cardno

,ROW_NUMBER() OVER

(

PARTITION BY LBB.cardno

ORDER BY LBB.dateout

) AS LBB_RID

,LBB.isbn

,LBB.dateout

,LBB.LDO_20030103

,LBB.yearpublished

,LBB.PUB_1920

,LBB.Title

FROM LOAN_BOOK_BASE LBB

WHERE LBB.LDO_20030103 = 1

OR LBB.PUB_1920 = 1

)

SELECT

FS.cardno

,FS.isbn

,FS.dateout

,FS.LDO_20030103

,FS.yearpublished

,FS.PUB_1920

,FS.Title

FROM FILTERED_SET FS

WHERE FS.LBB_RID = 1;

Results

cardno isbn dateout LDO_20030103 yearpublished PUB_1920 Title

--------------- -------------------- ---------- ------------ ------------- ----------- ----------------------

1193-18404 123-456-78-006 2002-12-31 1 2001-06-01 0 The Book of Topic 006

3939-18414 123-456-78-003 2012-07-01 0 1919-03-01 1 The Book of Topic 003

4977-18459 123-456-78-008 2003-01-01 1 2003-08-01 0 The Book of Topic 008

8050-18463 123-456-78-003 2003-01-05 0 1919-03-01 1 The Book of Topic 003

9440-18400 123-456-78-001 2012-01-15 0 1910-01-01 1 The Book of Topic 001

gulema.gerressu SSC Veteran Points: 208 More actions · Answer 4

Hi Natasha,

I dont fully understand the problem. I can't see anything wrong with your query.

The query will return date outs after 03/Jan/2004 for books published before 1920 which from what I understood is what you were after. Are you finding it is returning for books that were published after 1920?