3 Tier Relational DB's and Dynamic Tables

[RichardODreamweaver]

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!)

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

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

[MikeL7]

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

[Newsgroup_User]

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

[Newsgroup_User]

"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 🙂

[Newsgroup_User · Accepted solution]

"MikeL7" <webforumsuser@macromedia.com> wrote in message
news:fh58m3$dp8$1@forums.macromedia.com...
> Try this
> SELECT A.*,B.*,C.* FROM A, B, C WHERE A.AID = B.BAID AND B.BID = C.CBID
> WHERE etc... etc......

While this may work, you're not giving the database engine a chance to
optimize things,
this statement instructs that database to create a relation between all
records of a, and all records of b and all records of c, (a huge dataset)
which it then filters down.

It's more efficient if you want only certain records that match other
records by letting the database know that by using a JOIN statement.

Joris