By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,649 Members | 1,560 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,649 IT Pros & Developers. It's quick & easy.

Selecting data from three tables

KeredDrahcir
100+
P: 426
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Tiein.subMenuId 
  2. FROM Tiein, MainMenu, SubMenu 
  3. WHERE Tiein.mainMenuId=MainMenu.mainMenuId && Tiein.subMenuId=SubMenu.subMenuId 
  4. 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?
Feb 12 '10 #1
Share this Question
Share on Google+
3 Replies


Atli
Expert 5K+
P: 5,058
Hey.

What exactly is the rank? What does it represent?
Could you post an example of the data you are using?
Feb 12 '10 #2

KeredDrahcir
100+
P: 426
The rank is the order the menu items are displayed in.

The table in question looks like this:
Expand|Select|Wrap|Line Numbers
  1. +---------+------------+-----------+------+
  2. | tieinId | mainMenuId | subMenuId | rank |
  3. +---------+------------+-----------+------+
  4. |      19 |          9 |        25 |    1 |
  5. |      16 |          7 |        22 |    3 |
  6. |      18 |          7 |        24 |    2 |
  7. |       6 |          1 |        12 |    1 |
  8. |       5 |          7 |         9 |    1 |
  9. |      27 |         13 |        25 |    1 |
  10. |      32 |         13 |        28 |    2 |
  11. |      31 |          9 |        28 |    2 |
  12. |      51 |         72 |        25 |    1 |
  13. |      38 |         14 |        25 |    1 |
  14. |      40 |         12 |        25 |    1 |
  15. |      58 |         18 |        30 |    1 |
  16. |      42 |         12 |        28 |    2 |
  17. |      43 |         14 |        28 |    2 |
  18. |      52 |         72 |        28 |    2 |
  19. |      71 |          2 |        57 |    1 |
  20. |      72 |          2 |        58 |    2 |
  21. +---------+------------+-----------+------+
Is there anything else you need?
Feb 15 '10 #3

100+
P: 293
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!
Feb 15 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.