Select query for multiple tables and group by

[VenusCrystal]

Hi,

I have following table structures.

Table1

link - varchar
hlink - varchar
top-cap - varchar (FK)
sub-cap - varchar (FK)
lk - varchar(PK)

Table 2

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?

Thanks,
VC

[ZooOz]

Hi!

you need to apply some group function on the link, hlink and cap columns each in order to make the query correct.

[seslie]

Hi VC,

in this table,

Table1 link - varchar
hlink - varchar
top-cap - varchar (FK)
sub-cap - varchar (FK)
lk - varchar(PK)

Do the two foreign keys reference the same key on the second table? Please, could you put a sample of the kind of result you hope to obtain?

[seslie]

Btw, where you able to resolve your problem on the Oracle installation and how?