Join Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join Query

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2009-08-14 : 03:11:34

Hi Friends,

I have two tables one is Books and Other one is BooksFeatured

Books table has

BookId Userid Name Featured
1 2 AASA 1
2 3 ASAB 1
3 8 CASA 0
4 3 SSSD 1
5 7 ASAS 0
6 1 FAAS 1

BooksFeatured has

BFId UserId BookId FeaturedIn
1 3 4 G
2 1 6 No

What i need is ,i need the books which are all featured (Featured=1)
with Featuredin =G from Booksfeaured table and which are all not featured (Featured=0)

Expected Out
BookId Userid Name Featured

3 8 CASA 0
4 3 SSSD 1
5 7 ASAS 0

Since bookid 4 is featured and featuredin in 'G' ,
Bookid 3,5 are not featured

I tried with the below query

select * from BooksFeatured BF
Right join Books B on BF.BookId=B.BookId
where BF.FeaturedIn='G'
order by B.BookId desc

If you need more explanation,i will explain

Thanks in Advance

Kotti
Posting Yak Master

129 Posts

Posted - 2009-08-14 : 05:33:17

Hello Friends,

Please reply me about this query.

Your help will be much appreciated

Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-14 : 05:49:11

select t.* from Books t inner join BooksFeatured t1 on t1.userid = t.userid and t1.bookid = t.bookid
and t1.FeaturedIn = 'g'
union
select t.* from Books t where Featured = 0

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-14 : 05:52:51

[code]
DECLARE @Books TABLE (BookId INT, Userid INT, Name VARCHAR(4), Featured INT)
INSERT INTO @Books
SELECT 1,2,'AASA',1 UNION ALL
SELECT 2,3,'ASAB',1 UNION ALL
SELECT 3,8,'CASA',0 UNION ALL
SELECT 4,3,'SSSD',1 UNION ALL
SELECT 5,7,'ASAS',0 UNION ALL
SELECT 6,1,'FAAS',1

DECLARE @BooksFeatured TABLE (BFId INT, UserId INT, BookId INT, FeaturedIn VARCHAR(2))
INSERT INTO @BooksFeatured
SELECT 1, 3, 4, 'G' UNION ALL
SELECT 2, 1, 6, 'No'

SELECT b.BookId, b.Userid, b.Name, b.Featured
FROM @Books b left join @BooksFeatured f on b.userid=f.userid and b.bookid=f.bookid
WHERE b.Featured=0 or f.FeaturedIn='G'[/code]

Hope can help...but advise to wait pros with confirmation...

Kotti
Posting Yak Master

129 Posts

Posted - 2009-08-14 : 06:02:28

Hi waterduck

Thanks for your help.I got the answer with help of your query
Your help will be helpfull for me.
Once again thanks

Thanks for bklr too
Bklr when i use union i can't able to use order by

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-15 : 01:40:33

[code]hi, u can use order by clause when u use union statement
DECLARE @Books TABLE (BookId INT, Userid INT, Name VARCHAR(4), Featured INT)
INSERT INTO @Books
SELECT 1,2,'AASA',1 UNION ALL
SELECT 2,3,'ASAB',1 UNION ALL
SELECT 3,8,'CASA',0 UNION ALL
SELECT 4,3,'SSSD',1 UNION ALL
SELECT 5,7,'ASAS',0 UNION ALL
SELECT 6,1,'FAAS',1

DECLARE @BooksFeatured TABLE (BFId INT, UserId INT, BookId INT, FeaturedIn VARCHAR(2))
INSERT INTO @BooksFeatured
SELECT 1, 3, 4, 'G' UNION ALL
SELECT 2, 1, 6, 'No'

select t.* from @Books t inner join @BooksFeatured t1 on t1.userid = t.userid and t1.bookid = t.bookid
and t1.FeaturedIn = 'g'
union
select t.* from @Books t where Featured = 0
order by bookid desc
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources