Multiple joins with junction tables

Hi all,
I have a table listing case studies and another table that list outcomes.  A case study can have multiple outcomes so I created a junction table.
I want to run a SQL that will show the case once and each of the outcomes so far I have
SELECT caseSummaries.caseTitle, caseSummaries.caseSynopsis, RESULTS.resultText
FROM JNCT_RESULT_CASESUMMARY
JOIN caseSummaries ON JNCT_RESULT_CASESUMMARY.caseSummary_FK = caseSummaries.caseID
JOIN RESULTS ON JNCT_RESULT_CASESUMMARY.result_FK = RESULTS.result_ID
GROUP BY caseSummaries.caseID;

Open in new window

which gives me one row and only the first outcome of three. How can I show the others in the same row? Will I have to create temporary tables and how is that done?
Thanks,
C
LVL 5
cokefourAsked:
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.

Julian HansenCommented:
Try changing JOIN to LEFT JOIN
0
cokefourAuthor Commented:
Got the same result
0
Julian HansenCommented:
Then we need to see some table definition (create table statements)
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

cokefourAuthor Commented:
Assume from the question I have two tables
Case studies with three fields:
ID
Title
Synopsis
and another table containing Outcomes:
Apology
Compensation
Policy change
There is a many to many relationship and my sql needs to show the outcomes for each case study like this
Case 1 | Title | Synopsis | Apology|Compensation|Policy change
Case 2 |Title | Synopsis |  Apology|NULL|Policy change

assuming the Case 2 only has 2 outcomes.
At the moment without the GROUP BY or  SELECT DISTINCT  I get

Case 1 | Title | Synopsis | Apology
Case 1 | Title | Synopsis |Compensation
Case 1 | Title | Synopsis |Policy change
Case 2 | Title | Synopsis | Apology
Case 2 | Title | Synopsis |Policy change
0
Julian HansenCommented:
Ok but still need to see table definitions for the three tables

Case Studies (caseSummaries)
Outcomes (Results)
The table that joins them (JNCT_RESULT_CASESUMMARY)
0
cokefourAuthor Commented:
Thanks, I got the answer on stackoverflow
SELECT   caseSummaries.caseTitle, 
         caseSummaries.caseSynopsis, 
         GROUP_CONCAT(RESULTS.resultText)
FROM     JNCT_RESULT_CASESUMMARY
JOIN     caseSummaries ON JNCT_RESULT_CASESUMMARY.caseSummary_FK = caseSummaries.caseID
JOIN     RESULTS ON JNCT_RESULT_CASESUMMARY.result_FK = RESULTS.result_ID
GROUP BY caseSummaries.caseTitle, caseSummaries.caseSynopsis;

Open in new window

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
cokefourAuthor Commented:
I was able to get the answer elsewhere
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
MySQL Server

From novice to tech pro — start learning today.