skij
asked on
MySQL: Grouping results with JOIN
http://sqlfiddle.com/#!9/437c3/1/0
I need these results to be returned:
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
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"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Apple Hill Stories | 86019 | 0
Silly Sally | 86019 | 0
There's something missing in your explanation or maybe we're misunderstanding the concept.
ASKER
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.
I want results like this:
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
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 | -
zTable.sql
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:
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
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER