MySQL: Grouping results with JOIN

Posted on 2016-09-21
Last Modified: 2016-09-25!9/437c3/1/0

I need these results to be returned:
10 Smiles | 179736 | 1
Apple Hill Stories | 86019 | 0
Finding Phil | 179736 |1
Hand and Foot | 179736 | 1
Raising the Roof | (null) |(null)
Silly Sally | 86019 | 0

Open in new window

However, "10 Smiles" is included in the results 3 times.  Each item should only be included once, and if there is a match for ContactID = 179736 then X should be "1"
Question by:skij
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
LVL 58

Assisted Solution

by:Julian Hansen
Julian Hansen earned 50 total points
ID: 41808657
Which contactID should be used if there is more than one match and there is no 179736 in the results.
LVL 10

Author Comment

ID: 41808726
If there is no match for 179736 then the contactID should be null, as it is for "Raising the Roof"
LVL 23

Expert Comment

by:Ferruccio Accalai
ID: 41808761
So how do you explain these result?
Apple Hill Stories | 86019 | 0
Silly Sally | 86019 | 0

There's something missing in your explanation or maybe we're misunderstanding the concept.
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

LVL 10

Author Comment

ID: 41808842
Thank you for asking for clarification.  I want a list of Names for all available books, and I want to know if customer 179736  has already purchased that book or not.

This example should better demonstrate my need.

I have attached a sample dump.

SELECT `ZBooks`.`Name`,
    WHEN `ZOrders`.`ContactID` = 179736  THEN 'Customer 179736 Purchased This Book'
    else '-'
    END AS `Status`
from `ZBooks` 
Left Join `ZOrders` on `ZOrders`.`CourseID` = `ZBooks`.`CourseID`
ORDER BY `ZBooks`.`Name` ASC

Open in new window

The problem with this query is that one of the books is included three times in the list but it should only be included once.  

I want results like this:
10 Smiles		|	Customer 179736 Purchased This Book
Apple Hill Stories	|	-
Finding Phil		|	Customer 179736 Purchased This Book
Hand and Foot	|	Customer 179736 Purchased This Book
Raising the Roof	|	-
Silly Sally		|	-

Open in new window

LVL 23

Expert Comment

by:Ferruccio Accalai
ID: 41808865
I was working on a solution during your post so maybe what I'm suggesting is not exactly what you need but it's really near to your goal.

This is my query to get the result needed in first sight:
SELECT ZBooks.Name, ZOrders.ContactID, ContactID =179736 AS X
LEFT JOIN ZOrders ON ZBooks.CourseID = ZOrders.CourseID
) Z1
SELECT ZBooks.Name, ZOrders.ContactID, ContactID =179736 AS X
LEFT JOIN ZOrders ON ZBooks.CourseID = ZOrders.CourseID
ORDER BY ZBooks.Name ASC) Z2 ON ( Z1.Name = Z2.Name AND Z1.X < Z2.X ) 

Open in new window

LVL 10

Author Comment

ID: 41808903
Thank you, Ferruccio Accalai.  That produces the result set that I am looking for.  It uses 3 SELECT statements and 3 JOIN statements.   Is that the most performance efficient way to get these results?
LVL 23

Accepted Solution

Ferruccio Accalai earned 350 total points
ID: 41808927
In your case that's the only way to get the desired result. As You need to get just 1 record per book, what you have to do is a join between the first given query and another query where you skip the unwanted records. That's the normal way to solve a query matter like yours
LVL 41

Assisted Solution

Sharath earned 100 total points
ID: 41809769
try this.
SELECT b.Name, o.ContactID, o.ContactID = 179736 as X
  FROM ZBooks b
                    IF(@CourseID = CourseID, @rn := @rn+1, @rn := 1) rn,
                    IF(@CourseID <> CourseID, @CourseID := CourseID, -9999) dummy
               FROM ZOrders,(SELECT @rn := 1, @CourseID := -9999) t1
              ORDER BY CourseID, CASE ContactID WHEN 179736 THEN 0 ELSE 1 END, ContactID) o
   ON b.CourseID = o.CourseID AND o.rn = 1;

Open in new window!9/437c3/17

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Creating and Managing Databases with phpMyAdmin in cPanel.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

627 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