Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 67
  • Last Modified:

MySQL: Grouping results with JOIN

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
skij
Asked:
skij
3 Solutions
 
Julian HansenCommented:
Which contactID should be used if there is more than one match and there is no 179736 in the results.
0
 
skijAuthor Commented:
If there is no match for 179736 then the contactID should be null, as it is for "Raising the Roof"
0
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
skijAuthor Commented:
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
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
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
 
skijAuthor Commented:
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
 
Ferruccio AccalaiSenior developer, analyst and customer assistance Commented:
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
 
SharathData EngineerCommented:
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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now