3 Tier Relational DB's and Dynamic Tables

"Joris van Lier" <whizzrd@hotmail.com> wrote in message
news:fh6l1k$7om$1@forums.macromedia.com...
>
>
> "RichardODreamweaver" <webforumsuser@macromedia.com> wrote in message
> news:fh19sj$4tc$1@forums.macromedia.com...
>> I have three tables which I shall, for ease, call A, B and C.
>>
>> B is relational to A
>>
>> C is relational to B
>>
>> A1 - B1 - C1
>> C2
>> B2 - C3
>> C4
>> A2
>> A3 - B3 - C5
>> B4
>> B5 - C6
>> C7
>> C8
>>
>>
>> I have no trouble creating recordset links for one or the other
>>
>> SELECT A.*, B.*
>> FROM A INNER JOIN B ON A.AID = B.BAID
>> WHERE etc... etc......
>>
>> Similarly, I can create a recordset for the secondary link..
>>
>> SELECT B.*, C.*
>> FROM B INNER JOIN C ON B.BID = C.CBID
>> WHERE etc... etc......
>>
>> What I am trying to acheive however is a recordset with a 3 tier
>> relationship
>> and I can't figure the syntax.
>>
>> Somthing like...
>>
>> SELECT A.*, B.*, C.*
>> FROM A INNER JOIN B ON A.AID = B.BAID, B INNER JOIN C ON B.AID = C.BAID
>> WHERE etc... etc...... (This doesn't work!)
>
> that's almost right
>
> SELECT A.*, B.*, C.*
> FROM ((A INNER JOIN B ON A.AID = B.BAID) INNER JOIN C ON B.AID = C.BAID)
> WHERE etc... etc......
>
> I've added parentheses to show how the next join will operate on the
> first,
> the first join results in a relation of table a and b,
> you then join this "virtual table" to the third.
>
>> This would then result in a dynamic table with (B and C) in the same row
>> as it
>> propogates.
>>
>> Eventually, what I want to achieve is a simple attachment symbol on each
>> row
>> of B, based on something like....
>
> For the count to work with records that have no children you have to
> employ a LEFT join to return records from the RIGHT table even if no
> record on the LEFT side matches.

No MY other left :)

"RichardODreamweaver" <webforumsuser@macromedia.com> wrote in message
news:fh19sj$4tc$1@forums.macromedia.com...
> I have three tables which I shall, for ease, call A, B and C.
>
> B is relational to A
>
> C is relational to B
>
> A1 - B1 - C1
> C2
> B2 - C3
> C4
> A2
> A3 - B3 - C5
> B4
> B5 - C6
> C7
> C8
>
>
> I have no trouble creating recordset links for one or the other
>
> SELECT A.*, B.*
> FROM A INNER JOIN B ON A.AID = B.BAID
> WHERE etc... etc......
>
> Similarly, I can create a recordset for the secondary link..
>
> SELECT B.*, C.*
> FROM B INNER JOIN C ON B.BID = C.CBID
> WHERE etc... etc......
>
> What I am trying to acheive however is a recordset with a 3 tier
> relationship
> and I can't figure the syntax.
>
> Somthing like...
>
> SELECT A.*, B.*, C.*
> FROM A INNER JOIN B ON A.AID = B.BAID, B INNER JOIN C ON B.AID = C.BAID
> WHERE etc... etc...... (This doesn't work!)

that's almost right

SELECT A.*, B.*, C.*
FROM ((A INNER JOIN B ON A.AID = B.BAID) INNER JOIN C ON B.AID = C.BAID)
WHERE etc... etc......

I've added parentheses to show how the next join will operate on the first,
the first join results in a relation of table a and b,
you then join this "virtual table" to the third.

> This would then result in a dynamic table with (B and C) in the same row
> as it
> propogates.
>
> Eventually, what I want to achieve is a simple attachment symbol on each
> row
> of B, based on something like....

For the count to work with records that have no children you have to employ
a LEFT join to return records from the RIGHT table even if no record on the
LEFT side matches.

SELECT A.*, B.*, COUNT(c.CAID) AS CAID_COUNT
FROM ((A INNER JOIN B ON A.AID = B.BAID) LEFT JOIN C ON B.AID = C.BAID)
WHERE etc... etc......


> <?php if (C,TotalRecords <> NULL) { ?>
> <a href="/linkedattachments.php?BID=<?php echo $row_recordsetB['BID']; ?>"
> target="_blank">
> <img src="fileicon.gif" alt="Click to see attachments"></a>
> <?php } ?>
>
> Am I going about this the right way? Is this even clear? lol
>

Try this
SELECT A.*,B.*,C.* FROM A, B, C WHERE A.AID = B.BAID AND B.BID = C.CBID
WHERE etc... etc......