Solved

MySQL: Grouping results with JOIN

Posted on 2016-09-21
8
57 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 56

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 41

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

679 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