Inner Join probs - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Inner Join probs

Author

Topic

LearnEveryDay
Starting Member

37 Posts

Posted - 2005-11-23 : 07:19:41

Hi

I have two tables

Books and BooksAllocation

Books table has got

BookId Title

1 C#
2 SQL
3 ASP
4 c++

in the BooksAllocation table

BooksAllocationID BookID Price

1 2 Â£30.00
2 3 Â£10.00

I want the query to bring out all the records from table Books with price or price is null

So I have written a query

select * from Books B Left OUTER Join BooksAllocation BA On B.BookId = BA.BookId

It is not bringing all the records, it brings out only two records from BooksAllocation table

Can anyone help me where I am going wrong??

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 07:29:03

Something like this..

Select * From Books Where BookId In
(Select BooksAllocationID From BooksAllocation where pice = Someprice and price is null) ????

Sucess Comes to those who Believe in Beauty of their Dream..

LearnEveryDay
Starting Member

37 Posts

Posted - 2005-11-23 : 07:35:40

I tried your way. It does not bring out any records.

;(

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 07:38:55

opps.. sorry ..
Select * From Books Where BookId In
(Select BooksAllocationID From BooksAllocation where pice = Someprice or price is null)

or shoould be there.. and not And..

How about this ..???

replace some price .. but the value.. like 10 etc..

Sucess Comes to those who Believe in Beauty of their Dream..

LearnEveryDay
Starting Member

37 Posts

Posted - 2005-11-23 : 07:50:44

Now it only brings a record which matches the price

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 07:54:30

are there any records in the table booksallocation with the null value..

run this query and check that ..

Select * from booksallocation Where price is null

or may be it wont be null it will be blank..
then you need to run this query ..

Select * From Books Where BookId In
(Select BooksAllocationID From booksallocation Where ltrim(rtrim(price)) = '' or
price = someprice or price is null)

???

Sucess Comes to those who Believe in Beauty of their Dream..

LearnEveryDay
Starting Member

37 Posts

Posted - 2005-11-23 : 08:02:52

again it brings no records.

I run and checked out the bookAllocation there is no records with null or empty values. but it has got only two records which has got the prices

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 08:07:20

So what do u want then??

you want all the data from the book table.. ??

Can you post the required ouput??

Sucess Comes to those who Believe in Beauty of their Dream..

LearnEveryDay
Starting Member

37 Posts

Posted - 2005-11-23 : 08:07:20

May be I did not explain it clearly

If I say I want all the records where the bookid = 2

I want the recordset like

bookid title price

1 C# <null>
2 SQL 30
3 ASP 10
4 c++ <null>

How do I query??

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 08:11:12

oh i seee.

Select b.bookid,b.Booktitle,c.price From Books b left outer join
booksallocation c On b.BookId = c.BookID
???

Sucess Comes to those who Believe in Beauty of their Dream..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources