Selecting data from three tables

[KeredDrahcir]

I have three tables. One contains main menu items, one contains sub menu items and one contains the link between main menu and sub menu items.
Since more than one main menu item can have the same sub menu item I can't contain the sub menu item's parent in the sub menu table.

I want to list the ID numbers of the sub menu items in the table but only once but it needs to be ordered by the rank of the main menu items followed by their rank.
The statement I'm using is:

SELECT DISTINCT Tiein.subMenuId 
FROM Tiein, MainMenu, SubMenu 
WHERE Tiein.mainMenuId=MainMenu.mainMenuId && Tiein.subMenuId=SubMenu.subMenuId 
ORDER BY MainMenu.rank, Tiein.rank;

The results should be:
57
58
12
25
28
9
24
22
30

The results that come up are:
57
58
12
25
9
24
22
28
30

For some reason 28 is being moved down. Is it missing out it's instances before that time becuase it occure three times before?

Any ideas?

[Atli]

Hey.

What exactly is the rank? What does it represent?
Could you post an example of the data you are using?

[KeredDrahcir]

The rank is the order the menu items are displayed in.

The table in question looks like this:

+---------+------------+-----------+------+
| tieinId | mainMenuId | subMenuId | rank |
+---------+------------+-----------+------+
|      19 |          9 |        25 |    1 |
|      16 |          7 |        22 |    3 |
|      18 |          7 |        24 |    2 |
|       6 |          1 |        12 |    1 |
|       5 |          7 |         9 |    1 |
|      27 |         13 |        25 |    1 |
|      32 |         13 |        28 |    2 |
|      31 |          9 |        28 |    2 |
|      51 |         72 |        25 |    1 |
|      38 |         14 |        25 |    1 |
|      40 |         12 |        25 |    1 |
|      58 |         18 |        30 |    1 |
|      42 |         12 |        28 |    2 |
|      43 |         14 |        28 |    2 |
|      52 |         72 |        28 |    2 |
|      71 |          2 |        57 |    1 |
|      72 |          2 |        58 |    2 |
+---------+------------+-----------+------+

Is there anything else you need?

[madankarmukta]

From the result specified by you -

The results should be:
57
58
12
25
28
9
24
22
30

The results that come up are:
57
58
12
25
9
24
22
28
30


Could you please ellaborate more on what you want to say by "Is it missing out it's instances before that time becuase it occure three times before?
"
We may help you..

Thanks!