Help with SQL Query

[Azz Romaysa]

Please if somebody can help me with a query.
I've the following tables

  
mysql> select * from menu;
+----+---------------+----------------------------+-----------+
| id | title         | description                | published |
+----+---------------+----------------------------+-----------+
| 39 | Vrienden      | Hier zullen blaa van alles |         1 |
| 40 | Gekke Sites   | Ik weet het niet man!!!    |         1 |
| 41 | Online Stores | Dat is het                 |         1 |
+----+---------------+----------------------------+-----------+
 
mysql> select * from menu_item;
+----+-----------------------+-----------+----------+---------+
| id | title                 | published | ordering | menu_id |
+----+-----------------------+-----------+----------+---------+
| 25 | Damatec Web Solutions |         1 |        1 |      41 |
| 28 | Almorni               |         0 |        3 |      40 |
| 29 | Wie zijn wij          |         1 |        2 |      41 |
| 30 | Google                |         1 |        4 |      39 |
+----+-----------------------+-----------+----------+---------+
 
 
mysql> select * from page;
+----+--------------+---------+---------------------+---------------------+
| id | title        | content | created_date        | modified_date       |
+----+--------------+---------+---------------------+---------------------+
|  1 | Mijn Pagina  | Test1   | 00-00-0000 00:00:00 | 00-00-0000 00:00:00 |
|  2 | Wie zijn wij | Test2   | 00-00-0000 00:00:00 | 00-00-0000 00:00:00 |
|  3 | Contact      | Test3   | 00-00-0000 00:00:00 | 00-00-0000 00:00:00 |
+----+--------------+---------+---------------------+---------------------+
 
mysql> select * from page_menu_item;
+----+---------+--------------+
| id | page_id | menu_item_id |
+----+---------+--------------+
|  1 |       2 |           25 |
+----+---------+--------------+
 
mysql> select * from url;
+----+-----------------------+--------------+
| id | url                   | menu_item_id |
+----+-----------------------+--------------+
|  8 | <a href="http://website.com" target="_blank" rel="nofollow">http://website.com</a>    |           28 |
|  9 | <a href="http://yahoo.com" target="_blank" rel="nofollow">http://yahoo.com</a>      |           29 |
| 10 | <a href="http://www.google.com" target="_blank" rel="nofollow">http://www.google.com</a> |           30 |
+----+-----------------------+--------------+

I want a query to get the the following data if the menu item links to an internal page:
id | title | published | menu_id | page_id |

and the following data if the menu item links to an url:
id | title | published | menu_id | url_id | url


Thanks,

Abu
[ January 31, 2007: Message edited by: Azz Romaysa ]

[Jeanne Boyarsky]

Abu,
What do you have so far? Post that along with what you are stuck on and we can help you more.

[Abdulla Mamuwala]

hey azz, I agree with what Jeanne says you need to show some work man !!! By the looks of it you may want to check out the Table Join concept. chill

[Azz Romaysa]

Hello,

This is what I've so far

Select m.id, m.title, m.published, m.menu_id, p.page_id, u.id, u.url
From menu_item As m
Join page_menu_item As p On m.id = p.menu_item_id
Join url As u On m.id = u.menu_item_id
Where m.id = 25   

It always returns an empty table.

What I want is the following result when menu_item_id = 30:

  
id | title | published | menu_id | page_id | url_id | url
30 | Google | 1        | 39      |         | 10     | <a href="http://google.com" target="_blank" rel="nofollow">http://google.com</a>

Or this one when menu_item_id = 25:

id | title    | published | menu_id | page_id | url_id | url
25 | Damatec  | 1         | 41      | 2       |        | 

What is the query that I should have?
P.S. The menu_item cannot have an url_id and a page_id

Regards,
Azz
[ February 01, 2007: Message edited by: Azz Romaysa ]

[Jeanne Boyarsky]

Azz,
Your query is good first step. It sets up the join structure.

There is a concept called an outer join. The syntax varies by database. You can find the syntax by looking at your documentation or googling your database and "outer join". The concept is the same across databases.

[Arulanand Dayalan]

Hope this helps... I havent tested it...

  
select
menu.id as Menu id, result.C1 as Menu_iteam_id,result.C5 as title, 
result.C6 as Published ,result.C2 as Page_id , result.C3 as    
        U_id,result.C4 as Url
from 
Menu menu,
(
select 
m.menu_id as C1,p.id as C2,'null' as C3, 'null' as 
                        C4,m.tilte as C5,m.pulblised as C6
from
menu_item m,page p, page_menu_item pm
where
pm.menu_item_id = ?
and p.id = pm.page_id
and m.id = p.id
 
union all
 
select 
m.menu_id as C1,'null' as C2, u.id as C3, u.url as 
                        C4,m.tilte as C5,m.pulblised as C6
from 
menu_item m, url u
where 
u.menu_item_id = ?
m.id = u.menu_item_id
)as result
where
menu.id = result.C1

Thanks and Regards,
Arul.

[Arulanand Dayalan]

More Simpler one...

    
select
m.id, m.title, m.published, m.menu_id, p.page_id, u.id, u.url
from 
menu_item As m

left outer join
page_menu_item As p 
on 
m.id = p.menu_item_id 
left outer join
url As u 
On 
m.id = u.menu_item_id
where
m.id = 25
 

[Azz Romaysa]

Thank you guys,

This is the query that worked for me:

  
SELECT m.*, u.url 'url', p.page_id 'page_id' 
FROM menu_item m 
LEFT JOIN url u ON u.menu_item_id = m.id
LEFT JOIN page_menu_item p ON  p.menu_item_id = m.id
WHERE m.id = 25

Arulanand: your last query returned always an empty row.

My problem now is how can I update all these tables once. Could somebody help me?

Kind Regards,

Azz
[ February 06, 2007: Message edited by: Azz Romaysa ]