Solved

Multiple joins with junction tables

Posted on 2014-10-10
7
150 Views
Last Modified: 2014-10-20
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
0
Comment
Question by:cokefour
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 40374632
Try changing JOIN to LEFT JOIN
0
 
LVL 5

Author Comment

by:cokefour
ID: 40375160
Got the same result
0
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 40375435
Then we need to see some table definition (create table statements)
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 5

Author Comment

by:cokefour
ID: 40375814
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
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 40375949
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
 
LVL 5

Accepted Solution

by:
cokefour earned 0 total points
ID: 40382327
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
 
LVL 5

Author Closing Comment

by:cokefour
ID: 40391580
I was able to get the answer elsewhere
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

738 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