SQL query help........

[sanu]

please help me solving the problem i have three tables which has some common field
i want to display like

Life Style(cat) - user Punk
Fashion(subcat) - user Hans
Music(subcat) - user Punk

Expression(cat) - user Punk
Poem - user Rocky
Diaries - user Rocky

from the table below please help me

Table userlist

uid | username |

1 | Hans |
2 | Punk |
3 | Mary |
4 | Rocky |
5 |

Table catagory

cat_id | uid | cat_name

1 | 2 | Life Style
2 | 2 | Expression
3 | 1 | Opinion
4 | 3 |

Table subcatagory

sid | cat_id | uid | sid_name |

1 | 2 | 4 | Poem |
2 | 2 | 3 | Diaries |
3 | 1 | 1 | Fashion |

4 | 1 | 1 | Music |
5
6

[ratass2002]

Only works on MySql 4(where UNION is implemented):

(select a.name,b.name from cat a, userlist b where a.uid=b.uid) union (select c.name,d.name from subcat c, userlist d where c.uid=d.uid);

That's the closest I can get. I didn't find any info on how to add the cat and subcat to the result. I will be looking forward to know the answer.

if you want to try you real data:

create table userlist (uid int, name varchar(10));
create table cat (catid int, uid int, name varchar(10));
create table subcat (sid int,catid int, uid int, name varchar(10));
insert into userlist values(1,'Hans');
insert into userlist values(2,'Punk');
insert into userlist values(3,'Mary');
insert into userlist values(4,'Rocky');
insert into cat values(1,2,'Life Style');
insert into cat values(2,2,'Expression');
insert into cat values(3,1,'Opinion');
insert into subcat values(1,2,4,'Poem');
insert into subcat values(2,2,3,'Diaries');
insert into subcat values(3,1,1,'Fashion');
insert into subcat values(4,1,1,'Music');