Find Only Specific Data - 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

Find Only Specific Data

Author

Topic

gretty
Starting Member

6 Posts

Posted - 2009-10-29 : 19:11:30

hello

I have a task to find the titles of books that are only on sale from branch number 1 of 4 total branches.

quote:

List the book titles of books which are ONLY available (on hand) at Branch Number 1. Order the list alphabetically by book title.

But the output from my query involves titles from books that are on sale at branch bumber 1 and are on sale at others also?

My query:


select title, branch_num, B.book_code
from book B, inventory I
where B.book_code = I.book_code and branch_num not in(2,3,4)
order by title asc;

Another attempt that also doesnt work:

select title, branch_num, B.book_code
from book B, inventory I
where B.book_code = I.book_code and branch_num not in(2,3,4)
group by title , b.book_code, branch_num
having count(*) < 2
order by title asc;

gretty
Starting Member

6 Posts

Posted - 2009-10-30 : 04:45:32

Please help :P :(

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-10-30 : 05:32:40

quote:
Originally posted by gretty

hello

I have a task to find the titles of books that are only on sale from branch number 1 of 4 total branches.

quote:

List the book titles of books which are ONLY available (on hand) at Branch Number 1. Order the list alphabetically by book title.

But the output from my query involves titles from books that are on sale at branch bumber 1 and are on sale at others also?

My query:

select title, branch_num, B.book_code
from book B, inventory I
where B.book_code = I.book_code and branch_num not in(2,3,4)
order by title asc;

Another attempt that also doesnt work:
select title, branch_num, B.book_code
from book B, inventory I
where B.book_code = I.book_code and branch_num not in(2,3,4)
group by title , b.book_code, branch_num
having count(*) < 2
order by title asc;

I don't know what you up to but if you provide some sample data and expected output then it really helpful..
as per i understand you want details with only branch number=1

select title, branch_num, B.book_code
from book B, inventory I
where B.book_code = I.book_code and branch_num = 1
order by title asc;

Will do it..
or there is other branches also like 5,6,7

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-31 : 01:53:39

this should give you what you want:-

select title,
B.book_code
from book B
join (select book_code 
from inventory 
group by book_code
having min(branch_num)=1
and min(branch_num)=max(branch_num)
)I
on B.book_code = I.book_code 
order by title asc;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources