Solved

Multiple joins with junction tables

Posted on 2014-10-10
7
146 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 52

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 52

Expert Comment

by:Julian Hansen
ID: 40375435
Then we need to see some table definition (create table statements)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 52

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Creating and Managing Databases with phpMyAdmin in cPanel.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 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

16 Experts available now in Live!

Get 1:1 Help Now