Help with join

Hello, I have  2 tables products and authors.

product
     productid int,
     title varchar(100)

author
     productid int,  
     authorid int
     author varchar(100)


The primary key is productId
There may be many products in the product table for each author.
There may be many authors in the authors table for a product in the products table

I want to populate a table with the results that I will use to run fulltext queries against.

So if a product had multiple authors the product would appear in the results for each author

select p.title, p.productId, a.author
from products p, authors a

What type of join would get what I'm after?
gogetsomeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>many ... many
You'll need a many to many table to pull this off.  Something like...

CREATE TABLE ProductAuthors (
   id int identity(1,1),    -- Just so the table has a PK.  Not abundantly required. 
   productid int REFERENCES product(productid), 
   authorid int REFERENCES author(id)
)

Open in new window


then

ALTER TABLE author DROP productid

Open in new window


Then to join, it's Product to ProductAuthors to Author, depending on your needs.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
oheilCommented:
As you do not have a n-n-relation using an intermediate table, which connects your product and your authors table, I think, there is something wrong with your design.

You should have something like:
product
     productid int,
     title varchar(100)

author
     authorid int
     author varchar(100)

product2author
     id int,
     productid int,
     authorid int

In your design the feature
There may be many products in the product table for each author.
would need multiple entries in table author with same field authorid and author.

To answer your question with your design:
select * from author
join product
on author.productid = product.productid
0
gogetsomeAuthor Commented:
Actually I made a mistake. My authors table does contain productId as well. How does this change things?
Sorry for the confusion.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Brian CroweDatabase AdministratorCommented:
Unless the productId in Author refers to something else entirely it's not going to help you.  That would only allow for a single product per author.  You will need the intermediary table as described in the posts above.
0
oheilCommented:
This does not change things. You need to design a n-to-n-relation or many ... many, as jimhorn posted.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>My authors table does contain productId as well. How does this change things?
Use the ALTER TABLE statement I posted in my first comment to drop it.  Or in SSMS  open the table in design view and manually delete it.

>So if a product had multiple authors the product would appear in the results for each author
SELECT p.title, a.author
FROM product p
   JOIN productauthor pa oN p.productid = pa.productid
   JOIN authors a ON pa.authorid = a.authorid
0
gogetsomeAuthor Commented:
Thank you both for your assistance. I was able to get what I needed with your help.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.