Joins in MSSQL

[soonujose]

There are two tables ‘book’ & ‘author’.
We need a query to retrieve all books written by an author.
Primary key of ‘book’ is bookid, and of author.. it is ‘author id’.
There is another table ‘bookdetails’ which contains the field bookid, authorid

Kindly help..

Thank You..

[richasaraf]

There are two tables ‘book’ & ‘author’.
We need a query to retrieve all books written by an author.
Primary key of ‘book’ is bookid, and of author.. it is ‘author id’.
There is another table ‘bookdetails’ which contains the field bookid, authorid

Kindly help..

Thank You..

-------------------------------------------------------
Hello !
First consider table 'book' which has columns bookid, book_name and price.. out of which bookid is primary key.
Then consider table 'author' which has columns authorid and author_name out of which authorid is primary key.
Then consider table bookdetails which has columns bookid, authorid and publication..... i just assumed ok !
Now we want to retrieve all the books wriiten by an author say his authorid is 1 ..... then the query goes as follows.... you change the authorid accd to you !

query as follows :

 select 
        a.book_name, 
        b.author_name, 
        a.price 
from 
        book a, 
        author b, 
        bookdetails c
where 
        a.bookid = c.bookid and 
        c.authorid = b.authorid and 
        b.authorid = 1
 
 

----------------------------------------
This is the way joining is done ! :)

[ganeshpk]

hi,

select book.name,author.name from book,auther where book.bookid=auther.authorid

or

SELECT book.name,author.name
FROM  book
INNER JOIN auther
ON book.bookid=auther.authorid
 

bye
ganesh.k

[sumch_tarek]

Dear,
Your code is

 mysql_query("Select table1.fieldname,..........,table2.fieldname from table1,table2 where your matching condition");
 

[zykes]

if your using Enterprise Manager for the MS SQL, try using the view option. It's better you know...

[piers canadas]

Eek.
Richasarf's example was the best, though i prefer to use joins when writing in MS SQL. Thus:

SELECT
  bok.book_name,
  aut.author_name
FROM
  books AS bok
JOIN bookdetails AS bde ON
  bde.book_id = bok.id
JOIN authors AS aut ON
  bde.author_id = aut.id
WHERE
  author_name = 'authors name'
 

The examples with a straight join are plain wrong. they:
a) don't take into account that you clearly have a many-to-many relationship (ie an author writes many books, but equally a book may have many authors) and
b) will give you back nonsense data. ganesh's example will give you the author for a book where their ids coincidentally match but are in fact totally unrelated.

As for the suggestion that you use the enerprise managers visual tool, i recommend against it as writing your own sql straight will mean you can transport your skills across platforms (of course each platform has their own idiosyncracies, the JOIN is classic sql server, richasaraf's example is classic PSQL)

[abducted]

I am a beginner, and I am learning about normalization and many to many relationships. Selecting all books written by an author is admittedly over my head, but manageable. I can take these examples, decode the SQL, and apply them just fine.

But what I cannot figure out is a more complicated search, where the input is one book, and the results are the author who wrote that book, PLUS the rest of the books that the author wrote.

I can't do it without performing another separate query. I'm hoping it could be some kind of subselect, alias, temporary table, or self join, but don't know how to do it. The rest of the books by an author are filtered out because they don't match the first book. Is there a way to do this without an additional query?

[Metalzed]

I can't do it without performing another separate query. I'm hoping it could be some kind of subselect, alias, temporary table, or self join, but don't know how to do it. The rest of the books by an author are filtered out because they don't match the first book. Is there a way to do this without an additional query?

Dont know if this helps you but this is 2 diffrent ways to do the same thing

We have a TABLE called tbl_books
with two columns
bookname & author

We want to see all books the author that wrote Lord of the Rings have published

SELECT     bookname
FROM         tbl_books
WHERE     (author =
                          (SELECT     author
                            FROM     tbl_books     AS     tbl_books2
                            WHERE      (bookname = 'Lord of the ring')))

This is a then 2:nd way.

SELECT     tbl_books.bookname
FROM         tbl_books INNER JOIN
                      tbl_books AS tbl_books2 ON tbl_books.author = tbl_books2.author
WHERE     (tbl_books2.bookname= 'Lord of the ring')

Hop it way this you wanted help with

[abducted]

Ah!

Thanks for the reply. It helps tremedously in clarifying how these more complicated searches work. I can comprehend the subselect syntax easily, but I'm going to need to study that inner join a bit more.

[piers canadas]

Or use a derived table. The derived table gives you your desired authorID and you join to it as if it was a normal table

SELECT
  boo.*
FROM
  books
JOIN
(
  SELECT b.authorID
  FROM books AS b
  WHERE b.name = "lord of the rings"
) AS thisBook ON
 thisBook.authorID = books.authorID

[bharathk27]

if you can cleary explain me, i will give yu an exact query
i want to know what are the columns in the table books,authors,bookdetails and whar are the primary key in that tables

[NeoPa]

Have a look at SQL JOINs.

I'm afraid that much of what is posted in this thread is misleading and unhelpful. None of your tables should be linked without either a LEFT {OUTER} JOIN or an INNER JOIN. To use no joins at all would cause grievous and unnecessary overhead to the server.

[bharathk27]

what are you trying to say ?

[ck9663]

The post is more than four years old...

Relax everyone...

~~ CK

[NeoPa]

Bharathk27:
what are you trying to say ?

Exactly what I have posted. In case you're unsure, nothing I said was directed at anything you'd posted (in fact I'm pleased you posted and drew my attention to this thread).

@CK.
The last post date of threads is pretty immaterial. If you check the number of visits this thread has achieved you'll see it's probably still quite busy as far as searchers go. I have no intention of being disrespectful to any of our members, but I would hate to feel people are still searching on Bytes and finding suggestions to join tables using a Cartesian Product then filtering out all the rubbish.