Stumped. Totally... (but there must be a way!?)

[The Plankmeister]

Hi there...

I am having a whole bunch of trouble trying to design a certain query. I
have two tables (see a representation of them here:
http://www.plankmeister.org.uk/tables.html ) one called menu_data and one
called page_data.

menu_data contains a list of defined menus (menu_group) which may be sorted
according to the menu_group_display_order column.

page_data contains a list of page definitions. Each page belongs to a
menu_group. All the pages in a given group may be sorted according to the
page_display_order column. (for instance, a query to order all pages with a
menu_group equal to 1 in ascending order would produce:
Cats
Dogs
Fish
Squirrels)

What I am trying to do is to design a query that will tell me the
page_user_name of all the pages that sit at the 'top' of their menu group,
but ordered by the menu_group_display_order column.
So basically, it will order the data in menu_data by the
menu_group_display_order colum, then comparing menu_data.menu_group to
page_data.menu_group, will return the 'first' row after being ordered by
page_display_order, so that in the case of the data given, the return would
be:

Horses
Cats
Birds
Rabbits
I've tried UNION, GROUP BY, TOP 1, INNER JOIN, OUTER JOIN, SLIGHTLY TO THE
LEFT JOIN, ONLY IF ITS A WEDNESDAY JOIN... etc etc... But I just can't get
it to work! It either returns 40 rows, or one. I've spent a total of about
10 hours trying to design this query, and am losing what precious little
hair I have left at an alarming rate.

Hope someone can provide some insight!

Plankmeister.

[Igor Raytsin]

You could try this:

SELECT page_user_name
FROM page_data pd
INNER JOIN menu_data md ON pd.menu_group = md.menu_group AND
pd.page_display_order = 1
ORDER BY md.menu_group_dispaly_order
Hope it helps

"The Plankmeister" <pl******************@hotmail.com> wrote in message
news:40***********************@dread16.news.tele.d k...

Hi there...

I am having a whole bunch of trouble trying to design a certain query. I
have two tables (see a representation of them here:
http://www.plankmeister.org.uk/tables.html ) one called menu_data and one
called page_data.

menu_data contains a list of defined menus (menu_group) which may be sorted according to the menu_group_display_order column.

page_data contains a list of page definitions. Each page belongs to a
menu_group. All the pages in a given group may be sorted according to the
page_display_order column. (for instance, a query to order all pages with a menu_group equal to 1 in ascending order would produce:
Cats
Dogs
Fish
Squirrels)

What I am trying to do is to design a query that will tell me the
page_user_name of all the pages that sit at the 'top' of their menu group,
but ordered by the menu_group_display_order column.
So basically, it will order the data in menu_data by the
menu_group_display_order colum, then comparing menu_data.menu_group to
page_data.menu_group, will return the 'first' row after being ordered by
page_display_order, so that in the case of the data given, the return would be:

Horses
Cats
Birds
Rabbits
I've tried UNION, GROUP BY, TOP 1, INNER JOIN, OUTER JOIN, SLIGHTLY TO THE
LEFT JOIN, ONLY IF ITS A WEDNESDAY JOIN... etc etc... But I just can't get
it to work! It either returns 40 rows, or one. I've spent a total of about
10 hours trying to design this query, and am losing what precious little
hair I have left at an alarming rate.

Hope someone can provide some insight!

Plankmeister.

[The Plankmeister]

"Igor Raytsin" <n&*@cyberus.ca> wrote in message
news:40********@news.cybersurf.net...

You could try this:

SELECT page_user_name
FROM page_data pd
INNER JOIN menu_data md ON pd.menu_group = md.menu_group AND
pd.page_display_order = 1
ORDER BY md.menu_group_dispaly_order
Hope it helps
Indeed it does... Many thanks! However... a further complication is that the
lowest value in page_display_order doesn't always = 1. It could be 0, or
higher in certain circumstances.

"The Plankmeister" <pl******************@hotmail.com> wrote in message
news:40***********************@dread16.news.tele.d k...

Hi there...

I am having a whole bunch of trouble trying to design a certain query. I
have two tables (see a representation of them here:
http://www.plankmeister.org.uk/tables.html ) one called menu_data and one called page_data.

menu_data contains a list of defined menus (menu_group) which may be sorted

according to the menu_group_display_order column.

page_data contains a list of page definitions. Each page belongs to a
menu_group. All the pages in a given group may be sorted according to the page_display_order column. (for instance, a query to order all pages with a

menu_group equal to 1 in ascending order would produce:
Cats
Dogs
Fish
Squirrels)

What I am trying to do is to design a query that will tell me the
page_user_name of all the pages that sit at the 'top' of their menu

group, but ordered by the menu_group_display_order column.
So basically, it will order the data in menu_data by the
menu_group_display_order colum, then comparing menu_data.menu_group to
page_data.menu_group, will return the 'first' row after being ordered by
page_display_order, so that in the case of the data given, the return

would

be:

Horses
Cats
Birds
Rabbits
I've tried UNION, GROUP BY, TOP 1, INNER JOIN, OUTER JOIN, SLIGHTLY TO THE LEFT JOIN, ONLY IF ITS A WEDNESDAY JOIN... etc etc... But I just can't get it to work! It either returns 40 rows, or one. I've spent a total of about 10 hours trying to design this query, and am losing what precious little
hair I have left at an alarming rate.

Hope someone can provide some insight!

Plankmeister.

[Igor Raytsin]

Then it will look like this.

SELECT md.menu_group_display_order, pd1.page_user_name
FROM page_data pd1
INNER JOIN
(SELECT menu_group, MIN(page_display_order) min_page_display_order
FROM page_data
GROUP BY menu_group) pd2
ON pd1.menu_group = pd2.menu_group AND pd1.page_display_order =
pd2.min_page_display_order
INNER JOIN menu_data md ON pd1.menu_group = md.menu_group
ORDER BY md.menu_group_display_order
"The Plankmeister" <pl******************@hotmail.com> wrote in message
news:40***********************@dread16.news.tele.d k...

"Igor Raytsin" <n&*@cyberus.ca> wrote in message
news:40********@news.cybersurf.net...

You could try this:

SELECT page_user_name
FROM page_data pd
INNER JOIN menu_data md ON pd.menu_group = md.menu_group AND
pd.page_display_order = 1
ORDER BY md.menu_group_dispaly_order
Hope it helps
Indeed it does... Many thanks! However... a further complication is that

the lowest value in page_display_order doesn't always = 1. It could be 0, or
higher in certain circumstances.

"The Plankmeister" <pl******************@hotmail.com> wrote in message
news:40***********************@dread16.news.tele.d k...

Hi there...

I am having a whole bunch of trouble trying to design a certain query. I have two tables (see a representation of them here:
http://www.plankmeister.org.uk/tables.html ) one called menu_data and one called page_data.

menu_data contains a list of defined menus (menu_group) which may be

sorted

according to the menu_group_display_order column.

page_data contains a list of page definitions. Each page belongs to a
menu_group. All the pages in a given group may be sorted according to the page_display_order column. (for instance, a query to order all pages with

a

menu_group equal to 1 in ascending order would produce:
Cats
Dogs
Fish
Squirrels)

What I am trying to do is to design a query that will tell me the
page_user_name of all the pages that sit at the 'top' of their menu

group, but ordered by the menu_group_display_order column.
So basically, it will order the data in menu_data by the
menu_group_display_order colum, then comparing menu_data.menu_group to
page_data.menu_group, will return the 'first' row after being ordered by page_display_order, so that in the case of the data given, the return

would

be:

Horses
Cats
Birds
Rabbits
I've tried UNION, GROUP BY, TOP 1, INNER JOIN, OUTER JOIN, SLIGHTLY TO THE LEFT JOIN, ONLY IF ITS A WEDNESDAY JOIN... etc etc... But I just can't get it to work! It either returns 40 rows, or one. I've spent a total of about 10 hours trying to design this query, and am losing what precious little hair I have left at an alarming rate.

Hope someone can provide some insight!

Plankmeister.