A very simple select query question :-)

Hi everyone,
I'm having a small problem getting all the data I need from a table. I know
the answer is going to be really simple so if anyone could help I would be
very grateful.
Lets suppose I have a Books table. The Books table has an AuthorID column
to indicate the author of the book.
However, lets suppose that not all books have an author so the AuthorID may
be null.
Now, I need to Select all the books, including those that don't have an auth
or.
In order to get the name of the Author, I'm doing a join with the Authors
table.
The Problem:
The Select query I'm using is only returning rows for books that
have Authors. If the Author ID is null then the row is simply ommited
So my question is, how do I get all the books out, even the ones that dont
have a corresponding record in the Authors table? See, simple isnt it? :-)
I'm using an Inner Join construct to join the two tables and I have a sneaki
ng
suspicion that it is this that is causing the problem
The full text of the query would be something like:
SELECT *, Authors.FullName
FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
Or something like that.
I hope someone can help me. This is driving me nuts so I'd be deeply gratefu
l
Kindest Regards
SimonHi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Hi Simon,
SELECT Book.SomeColumn --,place different columns here don=B4t use *
Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID =3D Authors.AuthorID
HTH,jens Suessmeyer.|||Simon Harvey wrote:
> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I kno
w
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID ma
y
> be null.
> Now, I need to Select all the books, including those that don't have an au
thor.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books tha
t
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a snea
king
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grate
ful
> Kindest Regards
> Simon
Use an OUTER join:
SELECT Books.*,
Authors.FullName
FROM Books
LEFT OUTER JOIN Authors
ON Books.AuthorID = Authors.AuthorID ;
Books can have more than one author though, so I wouldn't expect to see
authorid in the Books table. More likely it belongs in a third table
that joins books to multiple authors (conceptually a many-to-many
relationship).
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
=> Left Join will give you the whole list including author_id is null.
If you need only the record with authorid null use the below query...
SELECT *, Authors.FullName
FROM Books LEFT JOIN Authors ON Books.AuthorID = Authors.AuthorID
WHERE (Books.AuthorID ='' OR Books.AuthorID IS NULL)
Thanks,
Sreejith
"Simon Harvey" wrote:

> Hi everyone,
> I'm having a small problem getting all the data I need from a table. I kno
w
> the answer is going to be really simple so if anyone could help I would be
> very grateful.
> Lets suppose I have a Books table. The Books table has an AuthorID column
> to indicate the author of the book.
> However, lets suppose that not all books have an author so the AuthorID ma
y
> be null.
> Now, I need to Select all the books, including those that don't have an au
thor.
> In order to get the name of the Author, I'm doing a join with the Authors
> table.
> The Problem:
> The Select query I'm using is only returning rows for books tha
t
> have Authors. If the Author ID is null then the row is simply ommited
> So my question is, how do I get all the books out, even the ones that dont
> have a corresponding record in the Authors table? See, simple isnt it? :-)
> I'm using an Inner Join construct to join the two tables and I have a snea
king
> suspicion that it is this that is causing the problem
> The full text of the query would be something like:
> SELECT *, Authors.FullName
> FROM Books INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
> Or something like that.
> I hope someone can help me. This is driving me nuts so I'd be deeply grate
ful
> Kindest Regards
> Simon
>
>|||Hello David,

> Simon Harvey wrote:
>
> Use an OUTER join:
> SELECT Books.*,
> Authors.FullName
> FROM Books
> LEFT OUTER JOIN Authors
> ON Books.AuthorID = Authors.AuthorID ;
> Books can have more than one author though, so I wouldn't expect to
> see authorid in the Books table. More likely it belongs in a third
> table that joins books to multiple authors (conceptually a
> many-to-many relationship).
You are quite right. It was just a fictional example off the top of my head
though. The real thing is somewhat more complicated so I wanted to create
a really simple example that was conceptually the same. Same thing goes with
the whole Select * thing.
It was just a very quick example to save my poor wee fingers! :-)
Thanks to you all for offering advice. I am very grateful
Kindest Regards
Simon