I am pretty new at using Firebird and have a beginner's question regarding multi-table queries.

I have 3 tables: A, B, and C.

Table A has 6 fields: ID, B1_ID, C1_ID, C2_ID, C3_ID, and C4_ID.

Table B has 2 fields: ID and Name.

Table C has 2 fields: ID and Name.

B1_ID is a foreign key reference to the ID field in table B.
C1_ID through C4_ID are foreign key references to the ID field in table C, referring to 4 separate rows (one per ID).

I would like to do a multi-table query, returning a single row from table A, but referencing B and C, so that I can get the Name fields from the corresponding tables B and C. If I only had a single C1_ID, without 2, 3, and 4, I would use, as I understand it, the following select:

Select A.ID, A.B1_ID, A.C1_ID, B.Name, C.Name from A, B, C
where B.ID = A.B1_ID and C.ID = A.C1_ID and A.ID = :ID;

Will SQL allow me to refer to multiple records from table C inside of a single row from table A? If so, what is the syntax that identifies which C.Name field goes with which ID field from table A?

Clearly, I could do this through multiple queries, one to get record A and perhaps B, and then 4 more to get the records from C, but that strikes me as unelegant and inefficient if the single-select capability exists.

I have been digging through the SQL documentation I have, but have been unable to come up with an answer on my own, and appreciate any help that anyone can give me.

Thanks in advance,

Dan.