Solved

Multiple joins with junction tables

Posted on 2014-10-10
7
144 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
  • 4
  • 3
7 Comments
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Try changing JOIN to LEFT JOIN
0
 
LVL 5

Author Comment

by:cokefour
Comment Utility
Got the same result
0
 
LVL 51

Expert Comment

by:Julian Hansen
Comment Utility
Then we need to see some table definition (create table statements)
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 5

Author Comment

by:cokefour
Comment Utility
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 51

Expert Comment

by:Julian Hansen
Comment Utility
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
Comment Utility
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
Comment Utility
I was able to get the answer elsewhere
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now