select distinct with conditions attached

[beary]

I'll try to explain the problem, but it may not make sense.

I have a mysql table with columns like id,title,speaker,day,month,year, topic etc.

At present, my syntax is

select * from files order by year, month, day

What this does is return all the entries as requested. However, initially, when an entry is made, it can have up to 3 distinct topics associated with it. So in the table, id1, id2 and id3 will have exactly the same info in every column except for the topic column.

Obviously, when I run the above select statement it prints out the details for each entry, id1, id2 and id3 as separate entries (as it's supposed to).

What I want is for mysql to look for any entries that are identical except for topic. And then instead of listing all 3 entries, list only 1 entry. When I print to the screen the information on topic, it would say "health, wealth, happiness".

At the moment there are 3 distinct entries. The first will say topic: "health". The second "wealth" etc etc.

I can't just use a select distinct title statement, as its possible that there may be two entries with exactly the same title, but different day, month and year.

Below I've put what it currently prints and what I need it to print. Hopefully someone(s) can interpret my request and point me in the right direction.

Thanks.

Currently:

Title: Making Money
Date: 07/09/28
Topic: money

~~~~~~~~~~~~~~~

Title: Making Money
Date: 07/09/28
Topic: happiness


What I need:

Title: Making Money
Date:07/09/28
Topic: money, happiness

[pradeep kaltari]

Currently:

Title: Making Money
Date: 07/09/28
Topic: money

~~~~~~~~~~~~~~~

Title: Making Money
Date: 07/09/28
Topic: happiness


What I need:

Title: Making Money
Date:07/09/28
Topic: money, happiness

Hi,
As per my understanding about your problem, this is what you are looking for:

Select title, date, GROUP_CONCAT(topic)
From <your_table>
Group By title, date
Order By title, date
 

Let me know if you were looking for something else.

-Pradeep

[beary]

Hi,
As per my understanding about your problem, this is what you are looking for:

Select title, date, GROUP_CONCAT(topic)
From <your_table>
Group By title, date
Order By title, date
 

Let me know if you were looking for something else.

-Pradeep

Just about there...

My statement is now "select *, GROUP_CONCAT(topic) from files group by title order by year, month, day"

It has successfully eliminated the repeated entries. However, next to Topic: it only shows the first topic, not the others.

So now it looks like...

Title: Making Money
Date:07/09/28
Topic: money

instead of what I need it to look like:

Title: Making Money
Date:07/09/28
Topic: money, happiness

I figure it's something minor to get that working.

Thanks so far for your help and I'm looking forward to the solution.

[pradeep kaltari]

Just about there...

My statement is now "select *, GROUP_CONCAT(topic) from files group by title order by year, month, day"

It has successfully eliminated the repeated entries. However, next to Topic: it only shows the first topic, not the others.

So now it looks like...

Title: Making Money
Date:07/09/28
Topic: money

instead of what I need it to look like:

Title: Making Money
Date:07/09/28
Topic: money, happiness

I figure it's something minor to get that working.

Thanks so far for your help and I'm looking forward to the solution.

Hi beary,
Please include "date" column in Group By clause and also make sure you have GROUP_CONCAT() function in the Select clause.

For more details on GROUP_CONCAT() please refer:
GROUP_CONCAT

In case of further doubts plz get back.

Thanks,
pradeep

[beary]

Hi beary,
Please include "date" column in Group By clause and also make sure you have GROUP_CONCAT() function in the Select clause.

It now reads "select *, GROUP_CONCAT(topic) from files group by year, month, day, title order by year, month, day"

Still no change...

I am clearly doing something wrong, so I thought I'd post the sql for creating the table, and inserting the records followed by my required output. Perhaps you can create the statement which will work after testing it. (If you would be so good!)

CREATE TABLE `files` (
`id` tinyint(6) NOT NULL auto_increment,
`year` text NOT NULL,
`month` text NOT NULL,
`day` text NOT NULL,
`series` text NOT NULL,
`title` text NOT NULL,
`speaker` text NOT NULL,
`topic` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;


INSERT INTO `files` (`id`, `year`, `month`, `day`, `series`, `title`, `speaker`, `topic`) VALUES
(12, '07', '01', '01', 'hope', 'money and happiness', 'J Avery', 'money'),
(13, '07', '01', '01', 'hope', 'money and happiness', 'J Avery', 'wealth')

What I need displayed is:

Title: Money and happiness
Date: 07/01/01
Speaker: J Avery
Topic(s): money, wealth

Any hope?

[pradeep kaltari]

It now reads "select *, GROUP_CONCAT(topic) from files group by year, month, day, title order by year, month, day"

Still no change...

I am clearly doing something wrong, so I thought I'd post the sql for creating the table, and inserting the records followed by my required output. Perhaps you can create the statement which will work after testing it. (If you would be so good!)

CREATE TABLE `files` (
`id` tinyint(6) NOT NULL auto_increment,
`year` text NOT NULL,
`month` text NOT NULL,
`day` text NOT NULL,
`series` text NOT NULL,
`title` text NOT NULL,
`speaker` text NOT NULL,
`topic` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;


INSERT INTO `files` (`id`, `year`, `month`, `day`, `series`, `title`, `speaker`, `topic`) VALUES
(12, '07', '01', '01', 'hope', 'money and happiness', 'J Avery', 'money'),
(13, '07', '01', '01', 'hope', 'money and happiness', 'J Avery', 'wealth')

What I need displayed is:

Title: Money and happiness
Date: 07/01/01
Speaker: J Avery
Topic(s): money, wealth

Any hope?

Hi beary,
I checked it with your table structure - the query works absolutely FINE.

Please try the following:

select year, month, day, title, speaker, GROUP_CONCAT(topic) as TOPICS 
from files group by year, month, day, title 
order by year, month, day
 

Your topics "money,wealth" will be under TOPICS column.

- Pradeep

[beary]

Hi beary,
I checked it with your table structure - the query works absolutely FINE.

Please try the following:

select year, month, day, title, speaker, GROUP_CONCAT(topic) as TOPICS 
from files group by year, month, day, title 
order by year, month, day
 

Your topics "money,wealth" will be under TOPICS column.

- Pradeep

Pradeep,

Thankyou so much for sticking with me on this one! It now works perfectly, as you said. It's nice to learn a completely new (to me) and very cool mysql thing.

I was wondering why it wouldn't work for me. The only essential difference between your first post in response and the last one is that in the last one you had group_concat(topic) as topics, whereas in the 1st the phrase "as topics" was not there. Perhaps that has been the issue.

Regardless, I'm grateful for your help!

[pradeep kaltari]

Pradeep,

Thankyou so much for sticking with me on this one! It now works perfectly, as you said. It's nice to learn a completely new (to me) and very cool mysql thing.

I was wondering why it wouldn't work for me. The only essential difference between your first post in response and the last one is that in the last one you had group_concat(topic) as topics, whereas in the 1st the phrase "as topics" was not there. Perhaps that has been the issue.

Regardless, I'm grateful for your help!

Your Welcome!

Glad to have helped you.

Rgds,
Pradeep