Query conversion quandry... MS Access -> MySQL

[The Plankmeister]

Hi,

I am converting some queries from stored procedures in MS Access to MySQL,
and have hit my first problem. The Access query references another stored
procedure, which is where the problem lies.

I have the following table data: (apologies if the columns aren't aligned
properly... it looks ok in a fixed-width font)

img_num img_page img_sect img_order

1 1 1 3
2 1 1 1
3 1 1 2
4 1 2 1
5 1 2 3
6 1 2 2
7 1 3 2
8 1 3 3
9 1 3 1
10 2 1 2
11 2 1 1
12 2 2 1
13 2 2 2
14 3 1 1
15 3 2 1
This relates to a numbered list of image files from 1 to 15. These images
are distributed throughout 3 different pages: 1, 2 and 3. On each of these
pages are separate sections. Each section has images belonging to it. So, as
you can see, on page 1, there are 3 numbered sections, each of which 'owns'
3 images.
What I am attempting to do is design a query that returns all of a given
image's 'siblings', which are then ordered by a column. So, for example, if
I were attempting to query for all the siblings of image number 1, the query
would return:

img_num
2
3
1

Or if I were to query for all the siblings of image number 10, the query
would return:

img_num
11
10

Or, for image number 6:

img_num
4
6
5

I wrote this query in MS Access using stored procedures and it worked
beautifully. But now I'm moving to MySQL that option isn't available to me
(I'm using v.4) and I just can't figure it out. I've tried joins and
subqueries, but I just cannot get my head around it. But I'm not the most
knowledgeable when it comes to database queries!
This is a simplified scenario, as the query needs to return several columns,
but I'm sure I've given all the info needed.

Hoping someone can help...

Plankmeister

[Bill Karwin]

The Plankmeister wrote:

What I am attempting to do is design a query that returns all of a given
image's 'siblings', which are then ordered by a column.

So siblings are defined by a matching img_page and img_sect?

SELECT Sibling.*
FROM MyImageTable AS Given INNER JOIN MyImageTable AS Sibling
ON Given.img_page = Sibling.img_page
AND Given.img_sect = Sibling.img_sect
WHERE Given.img_num = ?
ORDER BY Sibling.img_order

Substitute the img_num of your given image for the "?" and you're set.

Regards,
Bill K.

[The Plankmeister]

Bill.... You are a star. : )

"Bill Karwin" <bi**@karwin.com> wrote in message
news:ck*********@enews1.newsguy.com...

The Plankmeister wrote:

What I am attempting to do is design a query that returns all of a given
image's 'siblings', which are then ordered by a column.

So siblings are defined by a matching img_page and img_sect?

SELECT Sibling.*
FROM MyImageTable AS Given INNER JOIN MyImageTable AS Sibling
ON Given.img_page = Sibling.img_page
AND Given.img_sect = Sibling.img_sect
WHERE Given.img_num = ?
ORDER BY Sibling.img_order

Substitute the img_num of your given image for the "?" and you're set.

Regards,
Bill K.