[sql] Using DISTINCT inner join in SQL

I have three tables, A, B, C, where A is many to one B, and B is many to one C. I'd like a list of all C's in A.

My tables are something like this: A[id, valueA, lookupB], B[id, valueB, lookupC], C[id, valueC]. I've written a query with two nested SELECTs, but I'm wondering if it's possible to do INNER JOIN with DISTINCT somehow.

SELECT valueC
FROM C
INNER JOIN
(
    SELECT DISTINCT lookupC
    FROM B INNER JOIN
    (
        SELECT DISTINCT lookupB
        FROM A
    ) 
    A2 ON B.id = A2.lookupB
) 
B2 ON C.id = B2.lookupC

EDIT: The tables are fairly large, A is 500k rows, B is 10k rows and C is 100 rows, so there are a lot of uneccesary info if I do a basic inner join and use DISTINCT in the end, like this:

SELECT DISTINCT valueC
FROM 
C INNER JOIN B on C.id = B.lookupB
INNER JOIN A on B.id = A.lookupB

This is very, very slow (magnitudes times slower than the nested SELECT I do above.

Is this what you mean?

SELECT DISTINCT C.valueC
FROM 
C
INNER JOIN B ON C.id = B.lookupC
INNER JOIN A ON B.id = A.lookupB

SELECT DISTINCT C.valueC 
FROM C 
  LEFT JOIN B ON C.id = B.lookupC
  LEFT JOIN A ON B.id = A.lookupB
WHERE C.id IS NOT NULL

I don't see a good reason why you want to limit the result sets of A and B because what you want to have is a list of all C's that are referenced by A. I did a distinct on C.valueC because i guessed you wanted a unique list of C's.

EDIT: I agree with your argument. Even if your solution looks a bit nested it seems to be the best and fastest way to use your knowledge of the data and reduce the result sets.

There is no distinct join construct you could use so just stay with what you already have :)

I believe your 1:m relationships should already implicitly create DISTINCT JOINs.

But, if you're goal is just C's in each A, it might be easier to just use DISTINCT on the outer-most query.

SELECT DISTINCT a.valueA, c.valueC
FROM C
    INNER JOIN B ON B.lookupC = C.id
    INNER JOIN A ON A.lookupB = B.id
ORDER BY a.valueA, c.valueC