I'm trying to group by a column and have the entire row to be
returned. The result returned is wrong! I know this is a classic case
of a uncorrelated (strawberry) type of query that could be resolve
using a model similar to this:

SELECT t1.a, b, t1.c
from table t1
join (
SELECT a, MIN(b) AS b FROM table GROUP BY a
) AS t2
ON t1.a = t2.a AND t1.c = t2.c;

I've opened a post a long time ago in relation to something similar
but now that my model has changed, nothing works!

Let me explain:
I've created a db for a website that handles the discography on one
artist. The model is simple. I have albums (with covers) that contains
discs with tracks.
i.e. An compilation with 2 CD's and 1 CD/DVD ; that is 1 album with
the corresponding cover that contains 3 discs with tracks for each
disc. A track could also appear on other disc of another album...

What i want to do is output a list of all the tracks from the table
'track' (including those that have not been inserted in a disc) and
pick the album and cover information (and the info related to the
track) of the oldest album where the track appears. To pick the oldest
album i order by "date_released" and by "display_order" (some albums
could contain the same date, therefore i use the display_order column
to sort manually)

I have no idea how to use a strawberry query when i have to order 2
columns first.. ??

This is an image (with data in spanish but easy to understand) of
mysql that explains better my problem. For testing purposes, i've
selected one track and not all the tracks.
Track 475 returns 7 columns. I want to pick the one that is highlted
in red... that is the one with the oldest album released date and with
the greatest display_order.

http://www.rociojuradofanclub.com/to...lete/query.jpg

Any ideas how to achieve this type of strawberry query ?

Thanks in advance
Marco

On 2010-10-14 21:22, SM wrote:
[...]
Can you post a minimal set of tables (create statements) and some sample
data (insert statements) that illustrate the problem?

/Lennart

On Oct 15, 11:23*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:

here are all the tables involved in my query with some sample data.
Also, the query and the results and expected results:
http://www.rociojuradofanclub.com/to...lete/query.jpg


CREATE TABLE artwork (
artwork_id smallint unsigned not null primary key,
filename varchar(75) COLLATE utf8_unicode_ci not null
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO artwork (artwork_id, filename) VALUES
(1, 'rociosiempre.jpg'),
(2, 'senora_laantologiacompletadesusgrandescanciones.j pg'),
(3, 'yerbabuenaynopal.jpg');


CREATE TABLE album (
album_id smallint unsigned not null primary key,
name varchar(75) COLLATE utf8_unicode_ci not null,
subname varchar(75) COLLATE utf8_unicode_ci,
cover_id smallint unsigned,
description_HTML text,
format_id tinyint unsigned,
date_released date,
country_id tinyint unsigned,
recordlabel_id tinyint unsigned,
catalog_number varchar(25) COLLATE utf8_unicode_ci,
display_order tinyint unsigned not null DEFAULT 1,
edition_of_album smallint unsigned,
edition_note text,
single_of_album smallint unsigned
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO album (album_id, name, subname, cover_id,
description_HTML, format_id, date_released, country_id,
recordlabel_id, catalog_number, display_order, edition_of_album,
edition_note, single_of_album) VALUES
(8, 'Como las alas al viento', NULL, 8, NULL, 1, '1993-4-6', 1, 4,
'CDZ 81055', 1, NULL, NULL, NULL),
(9, 'Sevilla', NULL, 9, NULL, 1, '1991-1-1', NULL, NULL, NULL, 1,
NULL, NULL, NULL),
(10, 'Nueva navidad', NULL, 10, NULL, 1, '1990-12-1', NULL, NULL,
NULL, 1, NULL, NULL, NULL);


CREATE TABLE disc (
disc_id smallint unsigned not null primary key,
album_id smallint unsigned not null,
format_id tinyint unsigned,
label_id tinyint unsigned,
artwork_id smallint unsigned,
description varchar(255) COLLATE utf8_unicode_ci,
display_order tinyint not null DEFAULT 1
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO disc (disc_id, album_id, format_id, label_id, artwork_id,
description, display_order) VALUES
(1, 1, 1, 1, NULL, NULL, 1),
(2, 1, 2, 2, NULL, NULL, 2),
(3, 2, 1, 1, NULL, NULL, 1),
(4, 2, 1, 1, NULL, NULL, 2);

CREATE TABLE track (
track_id smallint unsigned not null primary key,
name varchar(100) COLLATE utf8_unicode_ci not null,
subname varchar(100) COLLATE utf8_unicode_ci,
subname2 varchar(50) COLLATE utf8_unicode_ci,
duration smallint unsigned,
sample_filename varchar(75) COLLATE utf8_unicode_ci,
copyright_HTML text,
description varchar(255) COLLATE utf8_unicode_ci,
lyrics_id smallint unsigned,
first_album smallint unsigned,
display_lyricslist_yn tinyint(1) not null DEFAULT 1,
audiotrack_yn tinyint(1) not null DEFAULT 1
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO track (track_id, name, subname, subname2, duration,
sample_filename, copyright_HTML, description, lyrics_id, first_album,
display_lyricslist_yn, audiotrack_yn) VALUES
(550, 'Qué no daría yo', NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, 0, 0),
(551, 'Fandangos', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0,
0),
(552, 'Como una ola', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
0, 1),
(553, 'Señora', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, 1);


CREATE TABLE disc_track (
disc_id smallint unsigned not null,
track_id smallint unsigned not null,
track_number tinyint unsigned not null DEFAULT 1,
location_id smallint unsigned,
side_group tinyint unsigned not null DEFAULT 1,
primary key(disc_id, track_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO disc_track (disc_id, track_id, track_number, location_id,
side_group) VALUES
(1, 80, 1, NULL, 1),
(1, 332, 2, NULL, 1),
(1, 466, 3, NULL, 1),
(1, 92, 4, NULL, 1),
(1, 382, 5, NULL, 1),
(1, 252, 6, NULL, 1);


Basically, I want to show a list off all the tracks (that's easy) and
inform the user where that track first appeared (first album released
in)? How do I find where the track was first released? Easy... find
the MIN(date_released) of the album. But, what if that track belongs
in more then one album with the same MIN date? Easy, use the attribute
"display_order" and find the lowest

One track could be in more then 1 disc and 1 disc can only belong to
one album. An album could have many discs. That's why one track shows
up in many albums.

In the image above, I was able to create a query that shows all the
tracks (for testing purposes, I’ve only showed track 475) and the
albums where the track appears in. Finding the oldest date of the
album and lowest display_order is when I get confused.

Hope this help... Please be free to ask anything.

Thanks in advance for all your help
Marco

On 16-10-10 06:34, SM wrote:
you are using GROUP BY on track.track_id

But you are not giving MySQL any direction on how to find the proper
disc_id, thats why its finding an undefined one

Somehow you have to sepcify that you want the disc_id (and artwork) that
belong to the MIN(date(released)

--
Luuk

On 16-10-10 09:41, Luuk wrote:
if you do this:
SET SESSION SQL_MODE='ANSI,ONLY_FULL_GROUP_BY';
before your query,
than it will give this error:
ERROR 1055 (42000): 'album.album_id' isn't in GROUP BY

--
Luuk

On 2010-10-16 06:34, SM wrote:
[...]
Is the data you provided correct? It appears as if the tracks don't
belong to a disc, i.e. they have never appeared on a album:

mysql> select track_id, disc_id from disc_track;
+----------+---------+
6 rows in set (0.00 sec)

mysql> select disc_id from disc;
+---------+
4 rows in set (0.00 sec)

mysql> select track_id from track;
+----------+
4 rows in set (0.00 sec)

Furthermore, the discs don't belong to any existing albums:

mysql> select album_id from disc;
+----------+
4 rows in set (0.00 sec)

mysql> select album_id from album;
+----------+
3 rows in set (0.00 sec)


/Lennart

On Oct 16, 11:28*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Is the data you provided correct? It appears as if the tracks don't
belong to a disc, i.e. they have never appeared on a album:

yes, it's correct. although, i just picked a couple or rows for each
table... not cosidering if it's a good sample or not...

i wish i could send you my sql file with the actual data...It's so
complicated to exposed my issue on google groups....
not sure what to do next

On Oct 16, 3:41*am, Luuk <L... (AT) invalid (DOT) lan> wrote:
how would i do that? i'm stuck here.... i cannot see clearly on how to
achieve my goal.... i feel i have to do a strawberry type of query,
but don't no how

On Oct 17, 11:20*pm, SM <servandomont... (AT) gmail (DOT) com> wrote:
i'm going to create another post with a better explanation of my
issue...a similar example... less complicated

On Oct 18, 4:20*am, SM <servandomont... (AT) gmail (DOT) com> wrote:
Your dataset is small, but it isn't representative. Just give us a few
more rows (for all tables) - like those that relate to ''Voy a
recobrar sua amor'