I have following table structures.
link - varchar
hlink - varchar
top-cap - varchar (FK)
sub-cap - varchar (FK)
lk - varchar(PK)
cap - varchar(PK)
caps - varchar
captype - varchar
top cap and sub-cap in Table1 are FK. They refer cap values of Table2, where cap is PK for Table2. captype in Table2 says if it is of type 'top' or 'sub'.
Now, i need to have a view where i get link and hlink values grouped based on top-cap and next grouping based on sub-cap.
I was trying the following way,
select top-cap, sub-cap, link, hlink, cap from Table1, Table2 GROUP BY top-cap, sub-cap;
But it didn't work.
Can anyone help me out with writing the query for the same?