Solved

MySQL: Grouping results with JOIN

Posted on 2016-09-21
8
52 Views
Last Modified: 2016-09-25
http://sqlfiddle.com/#!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"
0
Comment
Question by:skij
8 Comments
 
LVL 54

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.
0
 
LVL 10

Author Comment

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

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.
1
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 10

Author Comment

by:skij
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`,
 CASE
    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

zTable.sql
0
 
LVL 22

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 z1.* FROM (
SELECT ZBooks.Name, ZOrders.ContactID, ContactID =179736 AS X
FROM ZBooks
LEFT JOIN ZOrders ON ZBooks.CourseID = ZOrders.CourseID
ORDER BY ZBooks.Name ASC
) Z1
LEFT JOIN (
SELECT ZBooks.Name, ZOrders.ContactID, ContactID =179736 AS X
FROM ZBooks
LEFT JOIN ZOrders ON ZBooks.CourseID = ZOrders.CourseID
ORDER BY ZBooks.Name ASC) Z2 ON ( Z1.Name = Z2.Name AND Z1.X < Z2.X ) 
WHERE Z2.x IS NULL 
ORDER BY name

Open in new window

0
 
LVL 10

Author Comment

by:skij
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?
0
 
LVL 22

Accepted Solution

by:
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
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 100 total points
ID: 41809769
try this.
SELECT b.Name, o.ContactID, o.ContactID = 179736 as X
  FROM ZBooks b
  LEFT JOIN (SELECT *,
                    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


http://sqlfiddle.com/#!9/437c3/17
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

776 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