MySQL query with multiple tables

I have the next tables in MYSQL

---------
news
---------
id
title
description
pubdate

------------
newssource
------------
idnewssource
idnews
idsource

---------
sources
----------
idsource
source

------------
newscategory
------------
idnewscategory
idnews
idcategory

------------
category
------------
idcategory
category

How could I select all the sources and categories that depends from the given news ID, without duplicating the data from news table? I have the next query select, but for every row in newssource it duplicate the data from news table

Select
  news.idnews,
  news.title,
  news.description,
  sources.idsource,
  sources.source
From
  news Left Join
  newssource On newssource.idnews = news.idnews Left Join
  sources On newssource.idsource = sources.idsource

Open in new window


Or could be better to create a view in mysql and then work between a query and the view? in total are like 7 tables involved in the process.

Thanks
GeorgeTowersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Julian HansenCommented:
I don't understand - what are you expecting to see?

When you join two tables that are related in a one to many relationship the only way to represent the joined data when there is more than one child record for a parent is to duplicate the parent data

Parent
ID Parent Name
A  Alpha
B  Beta
C  Charlie

Child
ParentID ChildData
A           123
A           456
B           777
C           111

Select Parent.ID, Parent.Data, Child.ChildData from PArent Left Join Child on Parent.ID = Child.ParentID
You will get
A Alpha 123
A Alpha 456
B Beta  777
C Charlie 111

The first two rows repeat the parent's data - because that is the only way to show what parent the child belongs to?
lcohanDatabase AnalystCommented:
Assuming the news.id is actually news.idnews I believe that would be like below:

Select
  news.idnews,
  news.title,
  news.description,
  sources.idsource,
  sources.source
From
  news
  INNER Join newssource On news.idnews=newssource.idnews
  LEFT Join sources On newssource.idsource = sources.idsource

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
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
Query Syntax

From novice to tech pro — start learning today.