Noob Joining question - 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

Noob Joining question

Author

Topic

gmoney49
Starting Member

3 Posts

Posted - 2009-09-28 : 23:09:42

Hi guys,
Alright I have this sample bookstore database. The relevant tables in the Join are 'Book' table and 'Publisher' table.

Columns for the Book table:
-Book Code (P)
-Title
-PublisherCode
-Type
-Price Paperback

Columns for Publisher table:
-PublisherCode(P)
-PublisherName
-City

So I am trying to list the book code, book title, publisher code, and publisher name for all books. I'm having trouble with the syntax joining these two tables. Can I get some help? This is for SQL Server 2005 btw.

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-28 : 23:21:56

[code]
select b.book_code, b.title, b.publishercode, p.publishername
from book b
inner join publisher p on b.publishercode = p.publishercode
[/code]

KH
[spoiler]Time is always against us[/spoiler]

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-29 : 05:53:16

JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables

You should remember that. They are crucial. Do you feel like you have learnt something today? I do. EveryDay is a learning day.

[ /fail at query]

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-29 : 05:53:57

Inner Join = JOIN

btw.

[ /fail at query]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 06:08:25

quote:
Originally posted by winterh

JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows ~~when there is a match in one of the tables~~ from both tables with matching values coming together and for others having NULL value for unmatched fields

You should remember that. They are crucial. Do you feel like you have learnt something today? I do. EveryDay is a learning day.

[ /fail at query]

winterh
Posting Yak Master

127 Posts

Posted - 2009-09-29 : 06:18:06

I got pwned.

[ /fail at query]

gmoney49
Starting Member

3 Posts

Posted - 2009-09-29 : 18:43:13

quote:
Originally posted by khtan

select b.book_code, b.title, b.publishercode, p.publishername
from book b
inner join publisher p on b.publishercode = p.publishercode

KH
[spoiler]Time is always against us[/spoiler]

I appreciate the responses guys, thank you. The innerjoin worked great. My textbook, and the online resources I've came across are kind of vague in explaining the syntax so I wasn't sure. I kept getting ambiguous errors. In fact, my book says nothing of an inner join...are they not used that often?

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2009-09-29 : 19:15:54

Inner Joins are the most common that are used. What you will see in a lot of books is the following:

SELECT {columns}
FROM table1, table2
WHERE table1.key = table2.key;

This is also valid syntax using infixed notation and putting the join criteria in the where clause. Rewritten using ANSI-92 style join operators the above is rewritten as:

SELECT {columns}
FROM table1
INNER JOIN table2 ON table2.key = table1.key

I think this syntax is much cleaner and easier to read and understand. However, like I said - you'll find that most books use the first syntax for inner joins, and I am not really sure why.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources