I need help with a SQL Server query that pull data from a Many to Many relation and a One to Many relation

Posted on 2011-04-29
Medium Priority
Last Modified: 2012-05-11
Hi Experts,
I need help with a SQL Server query.
I have a many to many relation between tables Task and Project.  The linking table is Task_Project.
I also have a one to many relation between tables Task and Notes.
I want a query that will return:
All the Tasks (TaskName column), with or without Notes (Body Column), and all the Tasks with or without Projects (Project Name)  attached to them.
How can I do this?

I have included a relational diagram with this post to help visualize the relation between the tables. Relational Diagram
Question by:mainrotor

Accepted Solution

sshah254 earned 1000 total points
ID: 35492400
Select *
from ((tasks a left join notes b on a.taskid = b.taskid) left join task_project c on a.taskid = c.taskid) left join project d on c.projectid = d.projectid)

select the fields that you want, and the conditions if you have any.

LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 35492403
you need to LEFT JOIN the tasks table to the other tables:
LVL 41

Expert Comment

ID: 35493580
Is this an homework question?

Author Comment

ID: 35493734
No its not a homework question Sharath_123.  Thank you all.

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question