Avatar of GamesNET2
GamesNET2Flag for United Kingdom of Great Britain and Northern Ireland

asked on 

MySQL Select distinct query problem

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

MySQL Server

Avatar of undefined
Last Comment
Guy Hengel [angelIII / a3]
Avatar of Gerwin Jansen
Gerwin Jansen
Flag of Netherlands image

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
Avatar of GamesNET2
GamesNET2
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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

?!
ASKER CERTIFIED SOLUTION
Avatar of Gerwin Jansen
Gerwin Jansen
Flag of Netherlands image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Gerwin Jansen
Gerwin Jansen
Flag of Netherlands image

Hi GamesNET2, how are you doing? Made some progress?
Avatar of GamesNET2
GamesNET2
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Yes! Sorry I was so caught up in the success of it I forgot to come back, will award points tomorrow from a desk!
this article might give more insight about this kind of "problems":
https://www.experts-exchange.com/A_3203.html
MySQL Server
MySQL Server

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

49K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo