Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL: Grouping results with JOIN

Posted on 2016-09-21
8
Medium Priority
?
64 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
[X]
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
8 Comments
 
LVL 59

Assisted Solution

by:Julian Hansen
Julian Hansen earned 200 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 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.
1
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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 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 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 23

Accepted Solution

by:
Ferruccio Accalai earned 1400 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 400 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

722 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