Stan, thank you for the reply and the great information.

My apologies to the group for the mispost: I had intended to post it to the firebird-support list, but by mistake grabbed the wrong list address.

Thanks again,

Dan.


*********** REPLY SEPARATOR ***********

On 11/18/2003 at 7:27 PM stanw1950 wrote:

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.






___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/