distinct key word problem

[habesha]

I have three tables

Table1
col1, col2

Table2
col1, col2

table3
col1, col2, col3

col2 of table2 is a foreign key refering tabl1(col1)
col2 of table3 is a foreign key refering table2(col1)

I want to get for each col1 in table1, how many distinct values of col3 are there in table3

I tryed the query like this

SELECT COL1,
(SELECT COUNT(DISTINCT(TBL3.COL3))
FROM TABLE2 TBL2 INNER JOIN TABLE3 TBL3
ON TBL3.COL2 = TBL2.COL1
WHERE TBL2.COL2 = TBL1.COL1
) XY
FROM TABLE1 TBL1

because of the DISTINCT key word, it is costing me time

Is there other way to write this in a more efficient manner

Thanks,

 

[SQLBill]

Can you provide us some sample data and the results you would like to see? My first response would be, why can't us just use table3 for everything as COL1 is there?

SELECT col1, count(distinct col3)
FROM table3
GROUP BY col1

-SQLBill

Posting advice: FAQ481-4875

 

[Zathras]

Something like this?
[tt]
SELECT Table1.col1, Count(DISTINCT Table3.col3)
FROM Table1
JOIN Table2 on Table2.col2 = Table1.col1
JOIN Table3 on Table3.col2 = Table2.col1
GROUP BY Table1.col1
[/tt]