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 author.
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 sneaking
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 grateful
Kindest Regards
SimoHi 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 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 author.
> 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 sneaking
> 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 grateful
> 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 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 author.
> 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 sneaking
> 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 grateful
> Kindest Regards
> Simon
>
>