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.

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" <plankmeister_NOSPAM_@hotmail.com> wrote in message
news:40000829$0$27425$edfadb0f@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" <n&i@cyberus.ca> wrote in message
news:400020ac_1@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" <plankmeister_NOSPAM_@hotmail.com> wrote in message
> news:40000829$0$27425$edfadb0f@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.
> >
> >
> >
>
>

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" <plankmeister_NOSPAM_@hotmail.com> wrote in message
news:400022ea$0$27455$edfadb0f@dread16.news.tele.d k...
>
> "Igor Raytsin" <n&i@cyberus.ca> wrote in message
> news:400020ac_1@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" <plankmeister_NOSPAM_@hotmail.com> wrote in message
> > news:40000829$0$27425$edfadb0f@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.
> > >
> > >
> > >
> >
> >
>
>