Dan,
You are in the wrong group since this is not an IBO issue, but you
can use sub-selects. If you need ordering, use an integer for the
position of the field. For sql questions, there is a Borland
interbase sql newsgroup. There is probably a yahoo fb group where you
can also ask sql questions.

SELECT
ID,
B1_ID,
(SELECT NAME FROM B WHERE B.ID = B1_ID) AS B1NAME,
C1_ID,
(SELECT NAME FROM C WHERE C.ID = C1_ID) AS C1NAME,
C2_ID,
(SELECT NAME FROM C WHERE C.ID = C2_ID) AS C2NAME,
C3_ID,
(SELECT NAME FROM C WHERE C.ID = C3_ID) AS C3NAME,
C4_ID,
(SELECT NAME FROM C WHERE C.ID = C4_ID) AS C4NAME
FROM
A

/* optional: order by b1name */
ORDER BY
3

Regards,
Stan Walker




--- In IBObjects@yahoogroups.com, "Daniel G. Wilson" <dwilson@d...>
wrote:

> 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.