Solved

Multiple joins with junction tables

Posted on 2014-10-10
7
152 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 58

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 58

Expert Comment

by:Julian Hansen
ID: 40375435
Then we need to see some table definition (create table statements)
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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 58

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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

628 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