SQL join
-
I need to know how to use a join which will only take one result from the left hand table of the join and not create duplicates in the right hand (original) table.
For example I am querying a book table, and an authors table, but for the purposes of my output I want only to display ONE author per book and not two (as is sometimes the case with the data I am working with)
How can I accomplish this with just one sql query?
this is the query I am currently using which produces duplicates on the right:
select * from Book B
inner join bookContributor on B.bookID = bookcontributor.bookID
inner join contributor on bookcontributor.contributorid = contributor.contributorid
thanks in advance for your help
-
I'm assuming you mean you can have more than one author to a book and you want to show only 1 ?
It would help to have the structure of the tables..but you can use a subquery in the join to isolate a single author.
--M Kulangara
Mathew J Kulangara
sqladventures.blogspot.com -
Yes there can be more than one author per book, and I would very much like to isolate only one.
The Book table stores generic data about the book; Title, Description, ISBN etc. The BookContributor table is a link table which contains BookID's and ContributorID's - this is a many to many situation. The Contriburor table stores information about Authors, specifically the Names which I am interested in.
can you show me an example of a subquery that you mention to achieve this result ?
-
Tom if you could supply us with your table structure, some sample data and the expected results we could supply you with the informaton your requested.
Mike
-
SET NOCOUNT ON
DECLARE @Book TABLE
(
bookID INT,
BookName VARCHAR(100)
)
DECLARE @Contributor TABLE
(
contributorid INT,
Contributor VARCHAR(100)
)
DECLARE @bookContributor TABLE
(
bookID INT,
contributorid INT
)
INSERT INTO @Book VALUES (1, 'Book1')
INSERT INTO @Book VALUES (2, 'Book2')
INSERT INTO @Book VALUES (3, 'Book3')
INSERT INTO @Book VALUES (4, 'Book4')
INSERT INTO @Book VALUES (5, 'Book5')
INSERT INTO @Book VALUES (6, 'Book6')
INSERT INTO @Contributor VALUES (1, 'Contributor1')
INSERT INTO @Contributor VALUES (2, 'Contributor2')
INSERT INTO @Contributor VALUES (3, 'Contributor3')
INSERT INTO @Contributor VALUES (4, 'Contributor4')
INSERT INTO @Contributor VALUES (5, 'Contributor5')
INSERT INTO @Contributor VALUES (6, 'Contributor6')
INSERT INTO @bookContributor VALUES (1, 1)
INSERT INTO @bookContributor VALUES (2, 1)
INSERT INTO @bookContributor VALUES (3, 1)
INSERT INTO @bookContributor VALUES (4, 2)
INSERT INTO @bookContributor VALUES (5, 6)
INSERT INTO @bookContributor VALUES (6, 3)
INSERT INTO @bookContributor VALUES (6, 4)
INSERT INTO @bookContributor VALUES (6, 5)
/* Your query likely to give duplicates if the data is having duplicates */
select * from @Book B
inner join @bookContributor bc
on B.bookID = bc.bookID
inner join @contributor c
on bc.contributorid = c.contributorid
/* This query will give the unique author per book */
/* This is not correct. Put possible in TSQL */
/* If more than one author is there which one to choose. This query choses the first */
/* I would say you should correct the data */
/* There is no need for bookContributor table */
/* Just Add contributorid in the Book table */
SELECT B.BookName, C.Contributor
FROM
@Book B
INNER JOIN
(
SELECT bookID, MIN(contributorid) contributorid FROM @bookContributor
GROUP BY bookID) bc
ON B.bookID = bc.bookID
INNER JOIN
@contributor c
ON bc.contributorid = c.contributorid
Regards,
gova -
Hi Guys
Lets say our book table looks something like this:
BookID (int PK) Title ISBN
1 Into the Storm 12345
our Contributor table looks like:
ContributorID FirstName LastName
1 Tom Clancy
2 Fred Franks
and the BookContributor table looks like:
BookContributorID BookID ContributorID
1 1 1
2 1 2
Now when I execute my query:
select * from Book B
inner join bookContributor on B.bookID = bookcontributor.bookID
inner join contributor on bookcontributor.contributorid = contributor.contributorid
I am going to see TWO results returned for one Book. I actually only want to see one record coming back, and I dont particularly mind which Author comes out.
I need to know the solution not only for this scenario, but for others like this which I have encountered too, where you see duplication of results in the source table which is not wanted.
Previously I have solved this kind of problem by Hitting the DB twice which I am sure you agree is not ideal in a web situation.
thanks..
t
-
govinn, thanks!
Looks like you given me the lead I needed. I had no idea you could join a subquery like that.
-
OK. Anyone know how I might go about getting BOTH authors names into a single text/varchar field and have unique book results?
-
Your book contributor table holds the book ID, and the author ID, right?
And, when you query this table, you want the book ID, with only ONE author, even if others exist.
Let's assume that the first author entered is always the author that you want.
Select BookID, min(AuthorID)
from BookContributor
Group by BookID
We then build our query using this as a derived table.
select *
from BookTable b
join (Select BookID, min(AuthorID) as MinID
from BookContributor
Group by BookID) c
on b.BookID = c.BookID
-
SET NOCOUNT ON
DECLARE @Book TABLE
(
bookID INT,
ISBN VARCHAR(100)
)
DECLARE @Contributor TABLE
(
contributorid INT,
FirstName VARCHAR(100),
LastName VARCHAR(100)
)
DECLARE @bookContributor TABLE
(
BookContributorID INT,
bookID INT,
contributorid INT
)
INSERT INTO @Book VALUES (1, '12345')
INSERT INTO @Contributor VALUES (1, 'Tom', 'Clancy')
INSERT INTO @Contributor VALUES (2, 'Fred', 'Franks')
INSERT INTO @bookContributor VALUES (1, 1, 1)
INSERT INTO @bookContributor VALUES (2, 1, 2)
/* Your query */
select * from @Book B
inner join @bookContributor bookContributor on B.bookID = bookcontributor.bookID
inner join @contributor contributor on bookcontributor.contributorid = contributor.contributorid
/* Your Answer */
select * from @Book B
inner join (select bookID, min(contributorid) contributorid from @bookContributor
group by BookID) bookContributor on B.bookID = bookcontributor.bookID
inner join @contributor contributor on bookcontributor.contributorid = contributor.contributorid
Regards,
gova -
HTH
Regards,
gova -
id try something like this..
go
create table author_prec
(
author_prec_id int identity(1,1),
rank int not null,
contributor_id int not null,
constraint AK_author_pred_rank Unique(rank),
constraint FK_author_pred_cont_id foreign key(contributor_id)
references contributor(contributor_id)
)
go
--we rank authors so that the lowest rank will be given precedence
Select B.Title, q.AuthorName
from Books B
join
(select c.bookid,c2.AuthorName,min(rank)
from bookContributors C
join contributor C2
on C2.contributorid = C.contributorid
join author_prec pre
on pre.contributor_id = c2.contributor_id
group by c.bookid,c2.AuthorName
)Q
on Q.bookid = B.Bookid
where q.authorName in (select top 1 q.authorname from Q order by rank asc)
Mathew J Kulangara
sqladventures.blogspot.com -
I have a ContributorSequenceNumber column on the BookContributor table which has either and int, or a null in it. I need to have the lowest Int Number as my Author in the result set.
How would I do that?
t
-
Why do use null there ? What does that mean(that the book only has 1 author ?)
I would default that value to 0.
Then you can use what is below.
Select B.Title,C2.AuthorName
from Books B
join BookContributor C
on B.Bookid = C.Bookid
and c.Contributorid in
(select contributorid from (select Contributorid,min(isnull(ContributorSequenceNumber,0))
from BookContributor C2
where c2.Bookid = B.Bookid
group by contributorid)k)
join Contributor C2
on C2.contributorid = C.contributorid
group by B.Title,C2.AuthorName
Note ...I havent parsed this in QA..but hopefully the logic helps...
--M Kulangara
-
I not following the logic here
and I can't get that to parse in QA.
Think I need another coffee.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply