MySQL Select distinct query problem

GamesNET2
GamesNET2 used Ask the Experts™
on
Hi there,

I have the code below - when I run it it does not give me a DISTINCT list of 'title', however if i take away col_id_6 then it works,

Any ideas?


SELECT DISTINCT exp_channel_titles.title, exp_matrix_data.col_id_5
FROM exp_channel_titles
LEFT JOIN exp_channel_data ON exp_channel_titles.entry_id = exp_channel_data.entry_id 
LEFT JOIN exp_relationships ON exp_channel_data.entry_id = exp_relationships.rel_child_id
LEFT JOIN exp_relationships AS exp_rel ON exp_rel.rel_child_id = exp_relationships.rel_parent_id
LEFT JOIN exp_matrix_data ON exp_rel.rel_parent_id = exp_matrix_data.entry_id
WHERE exp_channel_data.channel_id = 1 AND exp_matrix_data.col_id_5 IS NOT NULL
ORDER BY exp_matrix_data.col_id_5 ASC
LIMIT 0, 10

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
You mean 5 I guess :)

What columns are in your output if you change to: select * from ...
hi,
try adding
 group by exp_channel_titles.title

Open in new window

instead of distinct
cus i think distinct a, b you give the cross distinct cvalues

Author

Commented:
Ineed it was 5, the GROUP BY works for the grouping, but then the ORDER BY doesn't work properly...

?!
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Topic Advisor
Most Valuable Expert 2016
Commented:
Try a 'select * ...' first, this is query 1, then build query 2 around it so you can select your distinct values, like this:

select DISTINCT exp_channel_titles.title, exp_matrix_data.col_id_5
from
(
  select *
  from exp_channel_titles
 ...
);
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
Hi GamesNET2, how are you doing? Made some progress?

Author

Commented:
Yes! Sorry I was so caught up in the success of it I forgot to come back, will award points tomorrow from a desk!
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
this article might give more insight about this kind of "problems":
http://www.experts-exchange.com/A_3203.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial